﻿using System;
using System.Collections.Generic;
using System.DirectoryServices.AccountManagement;
using System.IO;
using System.Reflection;
using System.Windows.Forms;
using Microsoft.Office.Core;
using Microsoft.Office.Interop.Excel;
using Tp.GUI.Dialog;
using Tp.Properties;
using TpAgentCore.Base;
using TpBaseObjects.Base;
using TpBaseObjects.Objects;
using TpData.Manager;
using TpLogger;
using TpTools;
using myOffice = Microsoft.Office.Interop;
using System.Text;
using TpExtensions;

namespace Tp {
    class Core {

        #region Members

        public enum ButtonDefaultValues {
            Button1 = 1,
            Button2 = 2
        }

        private enum faceIDs {
            NewSheet = 343,
            Export = 352,
            Import = 342,
            Dokument = 341,
            Bearbeitung = 345,
            Funktion = 341
        }

        public enum Customer {
            Verkauf = 1,
            Kunde = 2,
            Verloren = 3,
            Rewe = 4
        }

        public enum DocExt : int {
            [Extensions.StringValue(".docx")]
            DocX = 1,
            [Extensions.StringValue(".xlsm")]
            XlsM = 2,
            [Extensions.StringValue(".xlsx")]
            XlsX = 3

        }

        public enum DocType : int {
            [Extensions.StringValue("vk")]
            Verkauf = 1,
            [Extensions.StringValue("df")]
            Durchführung = 2,
            [Extensions.StringValue("an")]
            Angebot = 3,
            [Extensions.StringValue("ab")]
            Auftragsbestaetigung = 4,
            [Extensions.StringValue("ap")]
            Abnahmeprotokoll = 5,
            [Extensions.StringValue("ku")]
            Kundenkorrespondenz = 6,
            [Extensions.StringValue("li")]
            Lieferantenkorrespondenz = 7,
            [Extensions.StringValue("pb")]
            Projektbericht = 8,
            [Extensions.StringValue("te")]
            Technik = 9,
            [Extensions.StringValue("tk")]
            Telekonkontakt = 10,
            [Extensions.StringValue("re")]
            Rechnung = 11
        }

        public enum TpStatus : int {
            [Extensions.StringValue("Angebot")]
            Angebot = 1,
            [Extensions.StringValue("Verkauf")]
            Verkauf = 2,
            [Extensions.StringValue("Kunde")]
            Kunde = 3,
            [Extensions.StringValue("Verloren")]
            Verloren = 4
        }

        public enum TpKuerzel : int {
            [Extensions.StringValue("Admi")]
            Admi = 1,
            [Extensions.StringValue("Kund")]
            Kund = 2,
            [Extensions.StringValue("Lief")]
            Lief = 3,
            [Extensions.StringValue("Mark")]
            Mark = 4,
            [Extensions.StringValue("Rewe")]
            Rewe = 5,
            [Extensions.StringValue("Verk")]
            Verk = 6,
            [Extensions.StringValue("Verl")]
            Verl = 7
        }

        public enum SheetType : int {
            [Extensions.StringValue("Kalkulation")] //***
            Kalkulation = 1,
            [Extensions.StringValue("Angebot")] //***
            Angebot = 2,
            [Extensions.StringValue("Projektbericht")] //***
            Projektbericht = 3,
            [Extensions.StringValue("Abnahmeprotokoll")] //***
            Abnahmeprotokoll = 4,
            [Extensions.StringValue("Auftragsbestaetigung")] //***
            Auftragsbestaetigung = 5,
            [Extensions.StringValue("Abrechnung")] //***
            Abrechnung = 6,
            [Extensions.StringValue("Lieferschein")] //***
            Lieferschein = 7,
            [Extensions.StringValue("Rechnung")] //***
            Rechnung = 8,
            [Extensions.StringValue("EverySheet")] //***
            EverySheet = 99
        }

        #endregion

        #region Kontextmenü

        #region Neues Arbeitsblatt

        public static void Abnahmeprotokoll(Workbook wb, Worksheet sh, Worksheet ap, bool isRep, Logger log) {
            try {

                #region Members

                ProjectSheet sheet = new ProjectSheet(sh);
                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ZeileStartAbnahmeprotokoll;

                int copyRowRepPauschal = Settings.Default.ZeileStartAbnahmeprotokollReparaturPauschal;
                int copyRowRepRegie = Settings.Default.ZeileStartAbnahmeprotokollReparaturRegie;
                int copyRowRepProdukte = Settings.Default.ZeileStartAbnahmeprotokollReparaturProdukte;

                #endregion

                #region SetData

                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Kundenname, Convert.ToInt32(sheet.Plz).ToString(), wb.Name, sheet.Projekt, log)) {
                    return;
                }

                SetDataAbnahmeprotokoll(org, ap, sheet, isRep, log);

                #endregion

                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {
                    Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    if (selRow.RowType.ToUpper() == "DB") {
                        return;
                    }

                    if (selRow.RowLeistung == "1") {
                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                if (isRep) {
                                    if (selRow.Einheit == "Pa") {
                                        CreateApRepZeile(row, ap, copyRowRepPauschal, log);
                                        copyRowRepPauschal++;
                                    }
                                    if (selRow.Einheit == "Std") {
                                        CreateApRepZeile(row, ap, copyRowRepRegie, log);
                                        copyRowRepRegie++;
                                    }
                                    if (selRow.Einheit == "Stk") {
                                        CreateApRepZeile(row, ap, copyRowRepProdukte, log);
                                        copyRowRepProdukte++;
                                    }
                                } else {
                                    if ((copyRow > Settings.Default.ZeileApMaxFirstPage) & (copyRow < Settings.Default.ZeileApStartNextPage)) {
                                        copyRow = Settings.Default.ZeileApStartNextPage;
                                    }

                                    CreateApZeile(row, ap, copyRow, log);
                                    copyRow++;
                                }
                                break;
                            case "DB":
                                startRowSelect = true;
                                break;
                            default:
                                break;
                        }
                        selRow.RowLeistung = "";
                    }
                    startRow++;

                }
            } catch (Exception ex) {
                log.Add("Fehler bei Abnahmeprotokoll", ex);
            }
        }

        public static void Abrechnung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet abrechnung, Logger log) {
            ProjectSheet sheet = new ProjectSheet(abrechnung, true);

            #region Check Is a Calc Sheet

            if (!sheet.IsCalcSheet) {
                return;
            }

            #endregion

            #region Set rows

            foreach (ProjectRow row in sheet.MyRows) {
                Range r1 = abrechnung.Rows[row.RowNumber];
                if (!row.IsSummenZeile & !row.IsRabattZeile & row.RowType != "") {
                    if (row.JobDatum != new DateTime(1900, 1, 1)) {
                        if (row.JobDatum.Date <= DateTime.Now.Date) {
                            r1.Font.Bold = false;
                        } else {
                            r1.Font.Bold = true;
                        }
                    } else {
                        r1.Font.Bold = true;
                    }
                }

                if (row.SummenText.ToUpper() == "PROVISION VOM DB") {
                    Range rowSel = abrechnung.Rows[row.RowNumber];
                    Range sumVP = rowSel.Cells[1, Settings.Default.SpalteSummeVp];
                    sumVP.FormulaR1C1 = sumVP.Value;
                    sumVP.Font.Color = Settings.Default.Rot;
                    Range db = rowSel.Cells[1, Settings.Default.SpalteDb];
                    db.FormulaR1C1 = "=R" + row.RowNumber.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "/R" + (row.RowNumber - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "*-100";
                    db.Font.ThemeColor = XlThemeColor.xlThemeColorAccent2;
                }
            }

            #endregion



        }

        public static void AngebotNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet angebot, Logger log) {
            try {
                if (!IsCalcSheet(sh)) {
                    return;
                }

                #region Global

                CopyFaktoren(sh, angebot, log);
                GlobalVar.SummenStartRow = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow2 = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow3 = Settings.Default.StartCopyZeile;
                GlobalVar.MaxSummenHierachie = 0;
                GlobalVar.CopyRabatt = false;
                GlobalVar.RemoveAllProduktZeilen();
                GlobalVar.RemoveAllSummenZeilen();

                #endregion

                #region Members

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.StartCopyZeile;
                int startSummenRow = startRow;
                int lastSummenRow = 0;

                #endregion

                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {
                    sh.Activate();
                    startSummenRow = GlobalVar.LastSummenRow;
                    Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                    switch (strSelect) {
                        case "LE":
                        case "PR":
                            CopyProduktZeile(row, angebot, copyRow, false, log);
                            copyRow++;
                            startRow++;
                            break;
                        case "RA":
                            if (CopyRabattZeile(row, angebot, copyRow, log)) {
                                GlobalVar.CopyRabatt = true;
                                copyRow++;
                            }
                            startRow++;
                            break;
                        case "S":
                        case "SU":
                        case "S4":
                            if (CreateSummenZeile(row, selRow, sh, angebot, copyRow, lastSummenRow, log)) {
                                copyRow++;
                            }
                            startRow++;
                            break;
                        case "DB":
                            CreateDifference(sh, angebot, copyRow, log);
                            startRowSelect = true;
                            break;
                        default:
                            startRow++;
                            break;
                    }
                }
            } catch (Exception ex) {
                log.Add("AngebotNeu", ex);
            } finally {
                Range r1 = angebot.Columns["AH"];
                r1.ColumnWidth = Extensions.tpColumnWidth;
                SetKontextMenu(exApp, wb, angebot, log);
            }
        }

        public static void LieferscheinNeu(Workbook wb, Worksheet sh, Worksheet ls, Logger log) {
            try {

                #region Members

                ProjectSheet sheet = new ProjectSheet(sh);
                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ZeileStartLieferschein;

                #endregion

                #region SetData

                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Kundenname, Convert.ToInt32(sheet.Plz).ToString(), wb.Name, sheet.Projekt, log)) {
                    return;
                }
                SetDataLieferschein(org, ls, log);

                #endregion

                bool startRowSelect = false;
                while (startRowSelect == false & startRow < 10000) {
                    Range row = sh.Rows[startRow];
                    ProjectRow selRow = new ProjectRow(row);

                    if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                        return;
                    }

                    if (Tools.Nz(selRow.RowLeistung, "") == "1") {
                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                CreateLsZeile(row, ls, copyRow, log);
                                copyRow++;
                                break;
                            case "DB":
                                startRowSelect = true;
                                break;
                            default:
                                break;
                        }
                        selRow.RowLeistung = "";
                    }
                    startRow++;

                }
            } catch (Exception ex) {
                log.Add("Lieferschein", ex);
            }
        }

        public static void LoadFiles(InvoiceObject i, Logger log) {
            string filePath = Settings.Default.PathKunden + i.ProjektBezeichnung.Left(2) + @"\" + i.Kunde.Left(1) + @"\";
            string searchPattern = i.Kunde + i.ProjektBezeichnung + "??" + "ls??.*";
            string[] myFiles = Directory.GetFiles(filePath, searchPattern);

            if (myFiles.Length > 0) {
                List<string> file = new List<string>();
                foreach (string fileName in myFiles) {
                    file.Add(fileName);
                }
                i.Lieferscheine = file;
            }

            searchPattern = i.Kunde + i.ProjektBezeichnung + "??" + "ap??.*";
            myFiles = Directory.GetFiles(filePath, searchPattern);
            if (myFiles.Length > 0) {
                List<string> file = new List<string>();
                foreach (string fileName in myFiles) {
                    file.Add(fileName);
                }
                i.Abnahmeprotokolle = file;
            }
        }

        public static void ProjektBerichtNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet pb, Logger log) {
            try {
                ProjectSheet sheet = new ProjectSheet(sh);
                Organisation org = OrganisationDataManager.LoadOrganisation(sheet.Kundenname, sheet.Suchbegriff);
                if (!CheckOrgData(org, sheet.Kundenname, Convert.ToInt32(sheet.Plz).ToString(), wb.Name, sheet.Projekt, log)) {
                    return;
                }
                SetDataProjektBericht(org, pb, sheet, log);
                double calcTime = 0;

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.ProjektberichtStartZeile;
                sh.Activate();

                //Debug.Print("Start Pb : {0}", DateTime.Now);
                bool weiter = false;
                while (weiter == false & startRow < 10000) {

                    Range row = sh.Rows[startRow];

                    ProjectRow shRow = new ProjectRow(row);
                    if (Tools.Nz(shRow.RowLeistung, "") == "1") {
                        if (shRow.IsLeOrLa) {
                            //Leistung
                            Range r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteLeistung];
                            r1.Value = shRow.Leistung;

                            //Einheit
                            r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteEinheit];
                            r1.Value = shRow.Einheit;

                            //Menge
                            r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteMenge];
                            r1.Value = shRow.Menge;

                            //Pauschal-Zeit
                            r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteStd];
                            r1.Value = shRow.LeistungZeit;

                            //Leistung% AK: 12-04-25 Es, lt. Besprechung vom 12-04-20
                            //r1 = pb.Cells[copyRow, Settings.Default.ProjektberichtSpalteLeistungProzent];
                            //r1.Value = shRow.LeistungProzent;

                            //if(shRow.Einheit.ToUpper() == "PA") {
                            //    calcTime += shRow.LeistungZeit;
                            //}
                            shRow.RowLeistung = "";

                            copyRow++;
                        }
                        shRow.RowLeistung = "";
                    }
                    startRow++;
                    if (Tools.Nz(shRow.RowType, "").ToUpper() == "DB") {
                        weiter = true;
                    }
                }

                //if(calcTime > 0) {
                //    Range r1 = pb.Range[Settings.Default.ZellePbPauschZeit];
                //    r1.Value = calcTime;
                //}

            } catch (Exception ex) {
                log.Add("ProjektberichtNeu", ex);
            } finally {
                SetKontextMenu(exApp, wb, pb, log);
                pb.Activate();
            }
        }

        public static void ProjektNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            try {
                int sheetCount = wb.Sheets.Count;
                int newProjectID = GetMaxProjectNo(wb) + 1;
                string lastSheet = GetLastSheetName(wb);
                Worksheet af = wb.Sheets[lastSheet];
                Worksheet calc = GetCalcSheet(wb);
                Worksheet proj = wb.Sheets.Add(After: af, Type: Settings.Default.VorlageExcelKalkulation);
                proj.Name = "P" + newProjectID.ToString() + "k1";
                if (calc != null) {
                    proj.Range[Settings.Default.ZelleKundenname].Value = calc.Range[Settings.Default.ZelleKundenname].Value;
                    proj.Range[Settings.Default.ZellePlz].Value = calc.Range[Settings.Default.ZellePlz].Value;
                    proj.Range[Settings.Default.ZelleOrt].Value = calc.Range[Settings.Default.ZelleOrt].Value;
                    proj.Range[Settings.Default.ZelleStrasse].Value = calc.Range[Settings.Default.ZelleStrasse].Value;
                }
                CreateNewCalc(proj, log);
                SetKontextMenu(exApp, wb, proj, log);
            } catch (Exception ex) {
                log.Add("ProjektNeu", ex);
            } finally {

            }
        }

        public static void RechnungNeu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Worksheet rechnung, Logger log) {
            try {
                if (!IsCalcSheet(sh)) {
                    return;
                }

                #region Global

                CopyFaktoren(sh, rechnung, log);
                GlobalVar.SummenStartRow = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow2 = Settings.Default.StartCopyZeile;
                GlobalVar.SummenStartRow3 = Settings.Default.StartCopyZeile;
                GlobalVar.MaxSummenHierachie = 0;
                GlobalVar.CopyRabatt = false;
                GlobalVar.RemoveAllProduktZeilen();
                GlobalVar.RemoveAllSummenZeilen();

                #endregion

                #region Members

                int startRow = Settings.Default.ZeileStart;
                int copyRow = Settings.Default.StartCopyZeile;
                int startSummenRow = startRow;
                int lastSummenRow = 0;

                #endregion

                if (TpTools.Tools.ExistsContentInColumn(sh, Settings.Default.SpalteUserKennzeichnung, "1")) {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        sh.Activate();
                        startSummenRow = GlobalVar.LastSummenRow;
                        Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);

                        if (Tools.Nz(selRow.RowLeistung, "") == "1" | Tools.Nz(selRow.RowType, "").ToUpper() == "RA" | Tools.Nz(selRow.RowType, "").ToUpper() == "S4" | Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                            switch (strSelect) {
                                case "LE":
                                case "PA":
                                case "PR":
                                    CopyProduktZeile(row, rechnung, copyRow, false, log);
                                    selRow.SetExported("Ar");
                                    copyRow++;
                                    break;
                                case "RA":
                                    if (selRow.Ust) {
                                        if (CopyRabattZeile(row, rechnung, copyRow, log)) {
                                            copyRow++;
                                        }
                                    }
                                    break;
                                case "S4":
                                    if (CreateSummenZeile(row, selRow, sh, rechnung, copyRow, lastSummenRow, log)) {
                                        copyRow++;
                                    }
                                    break;
                                case "DB":
                                    CreateRechnungsDifference(wb, sh, rechnung, copyRow, log);
                                    startRowSelect = true;
                                    break;
                                default:
                                    break;
                            }
                            selRow.RowLeistung = "";
                        }
                        startRow++;
                    }
                } else {

                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        sh.Activate();
                        startSummenRow = GlobalVar.LastSummenRow;
                        Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);


                        string strSelect = (selRow.RowType == null ? "" : ((string)selRow.RowType).ToUpper());
                        switch (strSelect) {
                            case "LE":
                            case "PR":
                                if (!selRow.RechnungExported) {
                                    CopyProduktZeile(row, rechnung, copyRow, false, log);
                                    copyRow++;
                                }
                                startRow++;
                                break;
                            case "RA":
                                if (CopyRabattZeile(row, rechnung, copyRow, log)) {
                                    GlobalVar.CopyRabatt = true;
                                    copyRow++;
                                }
                                startRow++;
                                break;
                            case "S":
                            case "SU":
                            case "S4":
                                if (CreateSummenZeile(row, selRow, sh, rechnung, copyRow, lastSummenRow, log)) {
                                    copyRow++;
                                }
                                startRow++;
                                break;
                            case "DB":
                                CreateRechnungsDifference(wb, sh, rechnung, copyRow, log);
                                startRowSelect = true;
                                break;
                            default:
                                startRow++;
                                break;
                        }

                    }
                }

                ProjectSheet s1 = new ProjectSheet(sh, false);
                if (s1.IsReparatur) {
                    Range r1 = rechnung.Columns["AO"];
                    r1.NumberFormat = "0.00";
                }
            } catch (Exception ex) {
                log.Add("RechnungNeu", ex);
            } finally {
                SetKontextMenu(exApp, wb, rechnung, log);
            }
        }

        #endregion

        #region Export + Funktion

        public static void ExportAepü(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            ProjectSheet sheet = new ProjectSheet(sh);
            if (sheet.IsCalcSheet) {
                Workbook ae = null;
                try {
                    #region Check File exists

                    if (!File.Exists(Settings.Default.DateiAeSub)) {
                        string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiAeSub);
                        MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    #endregion

                    #region Check File is in use

                    if (Tools.FileIsInUse(Settings.Default.DateiAeSub)) {
                        string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiAeSub);
                        MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                        return;
                    }

                    #endregion

                    ae = exApp.Workbooks.Open(Settings.Default.DateiAeSub);
                    Worksheet aeSheet = ae.Sheets["AeSub"];
                    ProjectSheet mySheet = new ProjectSheet(sh);

                    int copyRow = 3;
                    Range row = GetFirstAeSubFreeRow(copyRow, aeSheet);
                    AeRow myRow = new AeRow(row);

                    UpdateAeDetails(mySheet, myRow, log);

                    string msg1 = String.Format(Resources.ExportOk, "(Aepü)");
                    MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);

                } catch (Exception ex) {
                    log.Add("Fehler in ExportAepü: ", ex);
                } finally {
                    if (ae != null) {
                        ae.Save();
                        ae.Close();
                    }
                }

            }
        }

        public static void ExportJobevidenz(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {

            ProjectSheet sheet = new ProjectSheet(sh);
            if (sheet.IsCalcSheet) {
                Workbook jeWb = null;
                Worksheet jeSheet = null;


                #region Check File exists

                if (!File.Exists(Settings.Default.DateiJesu)) {
                    string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiJesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                #region Check File is in use

                if (Tools.FileIsInUse(Settings.Default.DateiJesu)) {
                    string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiJesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                jeWb = exApp.Workbooks.Open(Settings.Default.DateiJesu);
                bool exported = false;
                int startRow = Settings.Default.ZeileStart;
                int startCopyRow = 3;
                try {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        int copyRow = 3;
                        Range row = sh.Rows[startRow];

                        ProjectRow selRow = new ProjectRow(row);
                        DateTime sDate = new DateTime(1900, 1, 1);
                        startRow++;

                        if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            break;
                        }

                        if (selRow.JobDatum == sDate | !Tools.SheetsExists(jeWb, selRow.Durchführender1)) {
                            continue;
                        }

                        if (selRow.IsReadyForJobevidenz) {
                            jeSheet = jeWb.Sheets[selRow.Durchführender1];

                            copyRow = GetFirstFreeRowNumber(startCopyRow, jeSheet);
                            sh.Activate();
                            CopyProduktZeile(row, jeSheet, copyRow, false, log, false, true, wb.FullName);
                            row.Font.Bold = true;
                            log.Add("Je: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender1 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")");
                            exported = true;

                            if (selRow.Durchführender2 != "") {
                                if (!Tools.SheetsExists(jeWb, selRow.Durchführender2)) {
                                    continue;
                                }
                                jeSheet = jeWb.Sheets[selRow.Durchführender2];
                                copyRow = GetFirstFreeRowNumber(copyRow, jeSheet);
                                sh.Activate();
                                CopyProduktZeile(row, jeSheet, startCopyRow, true, log, false, true, wb.FullName);
                                log.Add("Je: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender2 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")");
                                exported = true;
                            }
                            selRow.SetExported("Je");
                        }

                    }
                    if (exported) {
                        string msg1 = String.Format(Resources.ExportOk, "(Jobevidenz)");
                        MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }
                } catch (Exception ex) {
                    log.Add("ExportJobevidenz:" + wb.Name, ex);
                } finally {
                    if (jeWb != null) {
                        jeWb.Close(true);
                    }
                }
            }
        }

        public static void ExportLeistungen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            ExportLeistungen(exApp, wb, sh, log, true);
        }

        public static void ExportLeistungen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log, bool showMessage) {
            ProjectSheet sheet = new ProjectSheet(sh);
            if (sheet.IsCalcSheet) {
                Workbook leWb = null;
                Worksheet leSheet = null;

                #region Check File exists

                if (!File.Exists(Settings.Default.DateiLesu)) {
                    string msg = String.Format(Resources.DateiIstNichtVorhanden, Settings.Default.DateiLesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                #region Check File is in use

                if (Tools.FileIsInUse(Settings.Default.DateiLesu)) {
                    string msg = String.Format(Resources.DateiIstBereitsGeöffnet, Settings.Default.DateiLesu);
                    MessageBox.Show(msg, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                #endregion

                leWb = exApp.Workbooks.Open(Settings.Default.DateiLesu);
                bool exported = false;
                int startRow = Settings.Default.ZeileStart;
                int startCopyRow = 3;
                try {
                    bool startRowSelect = false;
                    while (startRowSelect == false & startRow < 10000) {
                        int copyRow = 3;
                        Range row = sh.Rows[startRow];
                        ProjectRow selRow = new ProjectRow(row);
                        DateTime sDate = new DateTime(1900, 1, 1);

                        if (Tools.Nz(selRow.RowType, "").ToUpper() == "DB") {
                            break;
                        }

                        if ((selRow.JobDatum == sDate) | (!Tools.SheetsExists(leWb, selRow.Durchführender1)) | (!selRow.IsLeOrLa)) {
                            startRow++;
                            continue;
                        }

                        if (selRow.IsReadyForExport) {
                            bool stellVertreter = false;

                            if (selRow.Stellvertreter != "") {
                                leSheet = leWb.Sheets[selRow.Stellvertreter];
                                stellVertreter = true;
                            } else {
                                leSheet = leWb.Sheets[selRow.Durchführender1];
                            }
                            copyRow = GetFirstFreeRowNumber(startCopyRow, leSheet);
                            sh.Activate();
                            CopyProduktZeile(row, leSheet, copyRow, false, log, stellVertreter, false, "");
                            row.Font.Bold = false;
                            log.Add("Le: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender1 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")");
                            exported = true;

                            selRow.Stellvertreter = "";
                            if (selRow.Durchführender2 != "") {
                                if (!Tools.SheetsExists(leWb, selRow.Durchführender2)) {
                                    continue;
                                }
                                leSheet = leWb.Sheets[selRow.Durchführender2];
                                copyRow = GetFirstFreeRowNumber(startCopyRow, leSheet);
                                sh.Activate();
                                CopyProduktZeile(row, leSheet, copyRow, true, log);
                                log.Add("Le: " + sheet.Kundenname + sheet.Projekt + ", " + sh.Name + ", " + selRow.Durchführender2 + ", " + selRow.RowText + " (" + selRow.JobDatum.ToString(Extensions.tpDateFormat) + ")");
                                exported = true;
                            }
                            if (selRow.IsReadyToStorno) {
                                row.Delete(Shift: XlDeleteShiftDirection.xlShiftUp);
                            } else {
                                selRow.SetExported("Le");
                                startRow++;
                            }
                        } else {
                            startRow++;
                        }

                    }

                    if (exported && showMessage) {
                        string msg1 = String.Format(Resources.ExportOk, "(Leistungen)");
                        MessageBox.Show(msg1, wb.Name, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
                    }

                } catch (Exception ex) {
                    log.Add("ExportLeistungen", ex);
                } finally {
                    if (leWb != null) {
                        leWb.Close(true);
                    }
                }


            }
        }

        #endregion

        #region Export + Funktion + Dokument

        public static void DokumentAngebot(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            bool wordStarted = Office.WordIsStarted(log);
            myOffice.Word.Application appWord = null;
            try {
                ProjectSheet sheet = new ProjectSheet(sh);
                if (sheet.IsCalcSheet) {
                    return;
                }

                Angebot anWindow = new Angebot(sheet.Kundenname, sheet.Suchbegriff);
                anWindow.ShowDialog();

                if (anWindow.Cancel) {
                    return;
                }

                if ((sheet.SheetType == (int)Core.SheetType.Angebot) | sheet.SheetType == 0) {
                    appWord = TpTools.Office.GetWordApplication(log);
                    string fileName = CreateFileName(sheet.Kundenname, sheet.Projekt, DocType.Angebot, Customer.Verkauf, DocExt.DocX, "p" + sheet.ProjektNummer.ToString());
                    myOffice.Word.Document mDoc = null;
                    int repeats = 0;
                    while (mDoc == null & repeats < 3) {
                        switch (anWindow.Versand) {
                            case (int)GlobalVar.VersandArt.Mail:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotMail, log);
                                break;
                            case (int)GlobalVar.VersandArt.Brief:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotBrief, log);
                                break;
                            case (int)GlobalVar.VersandArt.Fax:
                                mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordAngebotFax, log);
                                break;
                        }
                        repeats++;
                    }
                    if (mDoc == null) {
                        log.Add("mDoc = null");
                    }


                    mDoc.SaveAs2(FileName: fileName);
                    FillWordDocumentFields(appWord, mDoc, anWindow.SelectedType, anWindow.MyRecipient, log);
                    Range r1 = sh.Range[sh.PageSetup.PrintArea];
                    r1.Select();
                    r1.CopyPicture(XlPictureAppearance.xlPrinter, XlCopyPictureFormat.xlPicture);
                    foreach (myOffice.Word.Bookmark bm in mDoc.Bookmarks) {
                        if (bm.Name == "Angebot") {
                            bm.Select();
                            appWord.Selection.Paste();
                        }
                    }
                    appWord.Visible = true;
                    appWord.Activate();
                }
            } catch (Exception e) {
                log.Add(String.Format(Resources.DokumentAngebotErrorMsg, e.Message));
                string msg = "Es ist ein Fehler während des Exports in das Dokument aufgetreten.";
                MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                msg = "User: " + System.Environment.UserName + "\n\r" + "Zeit : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat) + "\n\r" + e.Message;
                Tools.SendMail(Settings.Default.ErrorMailFrom, Settings.Default.ErrorMailTo, "DokumentAngebot - Fehler", msg, null, log);
                if (appWord != null & !wordStarted) {
                    appWord.Quit();
                }
            }
        }

        public static void DokumentRechnung(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            log.Add("Start DokumentRechnung");
            bool wordStarted = Office.WordIsStarted(log);
            myOffice.Word.Application appWord = null;
            try {

                ProjectSheet inv = new ProjectSheet(sh);
                string abrName = "P" + inv.ProjektNummer + "r";
                if (!Tools.SheetsExists(wb, abrName)) {
                    string msg = "Abrechnung " + abrName + " wurde nicht gefunden!";
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }



                Worksheet abrSheet = wb.Sheets[abrName];
                ProjectSheet abr = new ProjectSheet(abrSheet);

                if (inv.SheetType != (int)SheetType.Rechnung) {
                    return;
                }

                Organisation org = OrganisationDataManager.LoadOrganisation(inv.Kundenname, inv.Suchbegriff);
                if (org == null) {
                    string msg = "Kunde wurde in Datenbank nicht gefunden.";
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }
                GlobalVar.currentOrganisation = org;

                InvoiceObject i = GetNewInvoiceWithData(inv, abr, org, 0, exApp, log);
                i.MyPersons = org.MyPersons;
                i.ProjektBezeichnung = inv.Projekt;

                if (i.Ust != 0) {
                    i.SachKonto = "400 00";
                } else {
                    i.SachKonto = "400 30; 400 41";
                }

                if (CheckLiquiIsOpen(i.Rechnungsdatum.Year.ToString().Right(2))) {
                    string msg = i.Rechnungsdatum.Year.ToString().Right(2) + @"\Liqui.xlsm ist geöffnet. Bitte zuerst diese Datei schließen!";
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    return;
                }

                Rechnung re = new Rechnung(i);
                re.ShowDialog();

                if (re.Cancel) {
                    log.Add("ShowDialog.Cancel");
                    return;
                }

                bool useOldRow = i.RechnungsNummer != 0;

                i.RechnungsNummer = GetRechnungsNummer(i, inv.Projekt, exApp, log);
                log.Add("GetRechnungsNummer:" + i.RechnungsNummer.ToString());

                if (i.RechnungsNummer <= 0) {
                    log.Add("Fehler bei GetRechnungsNummer:(" + i.RechnungsNummer.ToString() + ")");
                    return;
                }

                Write2Liqui(i, useOldRow, exApp, log);


                appWord = TpTools.Office.GetWordApplication(log);

                appWord = TpTools.Office.GetWordApplication(log);
                string fileName = CreateFileName(inv.Kundenname, inv.Projekt, DocType.Rechnung, Customer.Rewe, DocExt.DocX, "p" + inv.ProjektNummer.ToString(), i.RechnungsNummer, i.Rechnungsdatum.Year.ToString().Right(2));
                //Testen
                //fileName = @"T:\Rewe\12\Artp\Test\12" + i.RechnungsNummer.ToString("000") + ".docx";

                //abr.ActivateSheet();
                abr.WriteJob("Abrechnung", "Kunde", DateTime.Now, "Lf", i.Ma, i.RechnungsNummer, fileName, i.Rechnungsdatum.Year.ToString().Right(2) + i.RechnungsNummer.ToString("000") + "." + DocExt.DocX, log);
                abr.WriteJob("Abrechnung Reparatur", "Kunde", DateTime.Now, "Lf", i.Ma, i.RechnungsNummer, fileName, i.Rechnungsdatum.Year.ToString().Right(2) + i.RechnungsNummer.ToString("000") + "." + DocExt.DocX, log);

                if (i.Teilrechnung) {
                    abr.CopyJob("Abrechnung", "Kunde", log);
                    abr.CopyJob("Abrechnung Reparatur", "Kunde", log);
                }


                ExportLeistungen(exApp, wb, abr.MySheet, log, false);

                inv.ActivateSheet();

                myOffice.Word.Document mDoc = null;
                int repeats = 0;
                while (mDoc == null & repeats < 3) {
                    switch (i.RechnungVersand) {
                        case TpBaseObjects.Global.InvoiceSendType.Persoenlich:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungBriefPapier, log);
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Mail:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungMail, log);
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Post:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungBriefPapier, log);
                            break;
                        case TpBaseObjects.Global.InvoiceSendType.Fax:
                            mDoc = GetWordDocument(appWord, fileName, Settings.Default.VorlageWordRechnungMail, log);
                            break;
                    }
                    repeats++;
                }
                if (mDoc == null) {
                    log.Add("mDoc = null");
                }


                if (File.Exists(fileName)) {
                    File.Delete(fileName);
                }

                mDoc.SaveAs2(FileName: fileName);

                FillWordDocumentFields(appWord, mDoc, re.SelectedType, re.MyRecipient, log, i);
                mDoc.Save();
                Range r1 = sh.Range[sh.PageSetup.PrintArea];
                r1.Select();
                r1.CopyPicture(XlPictureAppearance.xlPrinter, XlCopyPictureFormat.xlPicture);
                foreach (myOffice.Word.Bookmark bm in mDoc.Bookmarks) {
                    if (bm.Name == "Rechnung") {
                        bm.Select();
                        appWord.Selection.Paste();
                    }
                }
                mDoc.Save();
                appWord.Visible = true;
                appWord.Activate();
            } catch (Exception ex) {
                log.Add(String.Format(Resources.DokumentRechnungErrorMsg, ex.Message));
                string msg = "DokumentRechnung: " + ex.Message;
                MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Error);
                msg = "User: " + System.Environment.UserName + "\n\r" + "Zeit : " + DateTime.Now.ToString(Extensions.tpDateFormat + " " + Extensions.tpTimeFormat) + "\n\r" + ex.Message;
                Tools.SendMail(Settings.Default.ErrorMailFrom, Settings.Default.ErrorMailTo, "DokumentAngebot - Fehler", msg, null, log);
                if (appWord != null & !wordStarted) {
                    //appWord.Quit();
                }
            }

        }

        #endregion

        #region Import Ka
        #endregion

        #region Bearbeitung Ka
        #endregion

        #endregion

        #region Workbook Events

        public static void WBActivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            SetKontextMenu(exApp, wb, sh, false, log);
        }

        public static void WBAfterSave(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbook) {
                exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
            }
        }

        public static bool WbBeforeClose(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool cancel) {

            if (!cancel) {
                if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbook) {
                    exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbook;
                }
            }

            return cancel;
        }

        public static bool WBBeforeSave(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool cancel) {
            if (wb.FileFormat != XlFileFormat.xlOpenXMLTemplateMacroEnabled) {
                ProjectSheet sheet = new ProjectSheet(sh);
                if (sheet.IsCalcSheet) {
                    cancel = !CheckCalcSheets(wb, sh);
                }
            }
            if (exApp.DefaultSaveFormat != XlFileFormat.xlOpenXMLWorkbookMacroEnabled) {
                exApp.DefaultSaveFormat = XlFileFormat.xlOpenXMLWorkbookMacroEnabled;
            }
            return cancel;
        }

        public static void WBDeactivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            DeleteMenuEntries(exApp);
        }

        public static void WbOpen(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            SetCalcMenu(exApp, wb, sh, log);
            SetKontextMenu(exApp, wb, sh, log);
        }

        public static void WbSheetActivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

            EnableDisableKontextMenu(exApp, sh);

            EnableDisableMenuGroups(exApp);

        }

        public static void WbSheetCalculate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

        }

        public static void WbSheetChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh);

            if ((exApp.ActiveCell.Address.Replace("$", "") == Settings.Default.ZelleKilometer) | (sheet.TempValue.Replace("$", "") == Settings.Default.ZelleKilometer)) {
                bool r1 = CheckKilometer(sh, false);
            }
        }

        public static void WbSheetDeactivate(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {

        }

        #endregion

        #region WorkSheet Events

        public static bool WSChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Range target, bool cancel, Logger log) {
            try {
                if (target.Address.Replace("$", "") == Settings.Default.ZelleKilometer) {
                    cancel = CheckKilometer(sh, cancel);
                }
            } catch (Exception ex) {
                log.Add("WSChange1", ex);
            }
            return cancel;
        }

        public static void WSChange(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            try {
                ProjectSheet sheet = new ProjectSheet(sh);
                if ((exApp.ActiveCell.Address.Replace("$", "") == Settings.Default.ZelleKilometer) | (sheet.TempValue.Replace("$", "") == Settings.Default.ZelleKilometer)) {
                    bool r1 = CheckKilometer(sh, false);
                }
            } catch (Exception ex) {
                log.Add("WSChange2", ex);
            }
        }

        #endregion

        #region Methods

        public static void AddDictionayFields(Dictionary<string, string> myDict, string tKey, object tValue) {
            AddDictionayFields(myDict, tKey, tValue, null);
        }

        public static void AddDictionayFields(Dictionary<string, string> myDict, string tKey, object tValue, object defaultValue) {
            if (tValue != null) {
                myDict.Add(tKey, tValue.ToString());
            } else {
                if (defaultValue != null) {
                    myDict.Add(tKey, defaultValue.ToString());
                }
            }
        }

        public static void AddMenuEntries(Logger log) {
            try {
                if (GlobalVar.MenuEntries.Count == 0) {

                    //Neues Arbeitsblatt (int)faceIds.NewSheet
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, GroupHeader = true, MenuName = "GruppeSheet", Caption = "Neues Arbeitsblatt", FaceID = (int)faceIDs.NewSheet, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAP", Caption = "Abnahmeprotokoll", OnAction = "VBSub_2", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAbrechnung", Caption = "Abrechnung", OnAction = "VBSub_16", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAngebot", Caption = "Angebot", OnAction = "VBSub_1", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenAuftragsbestätigung", Caption = "Auftragsbestaetigung", OnAction = "VBSub_14", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenLieferschein", Caption = "Lieferschein", OnAction = "VBSub_3", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenProject", Caption = "Projekt", OnAction = "VBSub_5", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenProjektBericht", Caption = "Projektbericht", OnAction = "VBSub_4", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 1, MenuName = "EinfügenRechnung", Caption = "Rechnung", OnAction = "VBSub_15", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });

                    //Funktion 
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 6, GroupHeader = true, MenuName = "GruppeFunction", Caption = "Funktion", FaceID = (int)faceIDs.Funktion, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 6, MenuName = "AuswahlKA", Caption = "Auswahl KA", OnAction = "VBSub_12", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = false });

                    //Export (int)faceIDs.Export
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, GroupHeader = true, MenuName = "GruppeExport", Caption = "Export + Funktion", FaceID = (int)faceIDs.Export, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "EinfügenAepü", Caption = "Aepü", OnAction = "VBSub_6", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "ExportLeistungenProjekt", Caption = "Leistungen Projekt", OnAction = "VBSub_7", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Kalkulation = true, Abrechnung = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 2, MenuName = "EinfügenLeistungen", Caption = "Leistungen", OnAction = "VBSub_8", FaceID = 0, Enabled = true, NewGroup = false, Visibly = false, Abrechnung = true, Kalkulation = true });

                    //Dokument (int)faceIDs.Dokument
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, GroupHeader = true, MenuName = "GruppeExportDokument", Caption = "Export + Funktion + Dokument", FaceID = (int)faceIDs.Dokument, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokAngebot", Caption = "Angebot Dokument", OnAction = "VBSub_9", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Angebot = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokAuftragsbestätigung", Caption = "Auftragsbestaetigung Dokument", OnAction = "VBSub_10", FaceID = 0, Enabled = false, NewGroup = false, Visibly = false, Auftragsbestaetigung = true });
                    GlobalVar.AddMenu(new MenuEintrag { GroupID = 3, MenuName = "DokRechnung", Caption = "Rechnung Dokument", OnAction = "VBSub_11", FaceID = 0, Enabled = true, NewGroup = false, Visibly = true, Rechnung = true });

                    //Import Ka (int)faceIDs.Import
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 4, GroupHeader = true, MenuName = "GruppeImportKa", Caption = "Import Ka", FaceID = (int)faceIDs.Import, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 4, MenuName = "ImportKa", Caption = "Import aus Ka", OnAction = "VBSub_12", FaceID = 0, Enabled = false, NewGroup = false, Visibly = true, Kalkulation = true });

                    ////Bearbeitung Ka (int)faceIDs.Import
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 5, GroupHeader = true, MenuName = "GruppeBearbeitungKa", Caption = "Bearbeitung Ka", FaceID = (int)faceIDs.Bearbeitung, Enabled = true, NewGroup = true, Visibly = true, EverySheet = true });
                    //GlobalVar.AddMenu(new MenuEintrag { GroupID = 5, MenuName = "BearbeiteKa", Caption = "Pr / Le - EInzeln - Gruppen", OnAction = "VBSub_13", FaceID = 0, Enabled = false, NewGroup = false, Visibly = true, Kalkulation = true });

                }
            } catch (Exception ex) {
                log.Add("AddMenuEntries", ex);
            }
        }

        public static void CopyFaktoren(Worksheet ka, Worksheet ab, Logger log) {
            try {
                ka.Activate();
                string rows = Settings.Default.ZeileFaktorenStart.ToString() + ":" + Settings.Default.ZeileFaktorenEnde.ToString();
                Range r1 = ka.Rows[rows];
                r1.Select();
                r1.Copy();

                ab.Activate();
                Range r2 = ab.Cells[1, 1];
                r2.Select();
                r2.PasteSpecial(XlPasteType.xlPasteFormulas, XlPasteSpecialOperation.xlPasteSpecialOperationNone);

                Range r3 = ab.Cells[1, 1];
                r3.Value = "An";

                r3 = ab.Cells[1, 3];
                r3.Value = "";
            } catch (Exception ex) {
                log.Add("CopyFaktoren", ex);
            }
        }

        public static void CopyProduktZeile(Range row, Worksheet target, int copyRow, bool sum, Logger log) {
            CopyProduktZeile(row, target, copyRow, sum, log, false, false, "");
        }

        public static void CopyProduktZeile(Range row, Worksheet target, int copyRow, bool sum, Logger log, bool stellvertreter, bool isJe, string sourceFullname) {
            try {
                row.Select();
                row.Copy();
                target.Activate();
                Range targetRows = target.Rows[copyRow];
                targetRows.Select();
                targetRows.PasteSpecial(XlPasteType.xlPasteValues, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                targetRows.PasteSpecial(XlPasteType.xlPasteComments, XlPasteSpecialOperation.xlPasteSpecialOperationNone, SkipBlanks: false, Transpose: false);
                if (!sum) {
                    GlobalVar.AddProduktZeile("R" + copyRow.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString());
                }
                if (stellvertreter) {
                    //targetRows.Font.Color = Settings.Default.Rot;
                    Range r1 = row.Cells[1, "I"];
                    Range t1 = targetRows.Cells[1, "N"];
                    t1.Value = r1.Value;

                }
                if (isJe) {
                    SetSpecialRowSettings(sourceFullname, targetRows);
                }

            } catch (Exception ex) {
                log.Add("CopyProduktZeile", ex);
            }
        }

        public static bool CopyRabattZeile(Range row, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectRow selRow = new ProjectRow(row);
                if (selRow.SummeVp != 0 | selRow.Ust) {
                    CopyProduktZeile(row, target, copyRow, false, log);
                    Range r1 = target.Rows[copyRow];
                    Range rab1 = r1.Cells[1, Settings.Default.SpalteRabatt];
                    rab1.Value = selRow.RabattProzent;
                    Range rab2 = r1.Cells[1, Settings.Default.SpalteSummeVp];
                    if (selRow.Ust) {
                        rab2.FormulaR1C1 = "=R" + ((int)(copyRow - 1)).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + "*R" + copyRow.ToString() + "C" + Settings.Default.SpalteRabatt.ToString() + "/100";
                        target.Names.Add("Umsatzsteuer", rab2);
                    } else {
                        rab2.Value = selRow.SummeVp;
                    }
                    return true;
                }
            } catch (Exception ex) {
                log.Add("CopyRabattZeile", ex);
            }
            return false;
        }

        private static void CreateApZeile(Range row, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectRow selRow = new ProjectRow(row);

                if (copyRow >= Settings.Default.ZeileApMaxSecondPage) {
                    Range newRow = target.Rows[copyRow];
                    newRow.Select();
                    newRow.Copy();
                    newRow.Select();
                    newRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                }

                Range prLeText = target.Range[Settings.Default.SpalteApProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                Range einheit = target.Range[Settings.Default.SpalteApEinheit + copyRow.ToString()];
                einheit.Value = selRow.Einheit;

                Range menge = target.Range[Settings.Default.SpalteApMenge + copyRow.ToString()];
                menge.Value = selRow.Menge;

                Range targetRow = target.Rows[copyRow];
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
            } catch (Exception ex) {
                log.Add("CreateApZeile", ex);
            }
        }

        private static void CreateApRepZeile(Range row, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectRow selRow = new ProjectRow(row);
                Range prLeText = target.Range[Settings.Default.SpalteApRepProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                if (selRow.Einheit.ToUpper() != "STD") {
                    Range einheit = target.Range[Settings.Default.SpalteApRepEinheit + copyRow.ToString()];
                    einheit.Value = selRow.Einheit;

                    Range menge = target.Range[Settings.Default.SpalteApRepMenge + copyRow.ToString()];
                    menge.Value = selRow.Menge;
                }
            } catch (Exception ex) {
                log.Add("CreateApRepZeile", ex);
            }
        }

        public static void CreateDifference(Worksheet ka, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectSheet sheet = new ProjectSheet(ka);
                Range r1 = target.Cells[copyRow, Settings.Default.SpalteText];
                r1.Value = "Differenz";
                r1.Font.Bold = true;

                Range r2 = target.Cells[copyRow, Settings.Default.SpalteSummeVp];
                if (sheet.HasNames) {
                    r2.FormulaR1C1 = "=R" + Convert.ToString(copyRow - 1) + "C" + Settings.Default.SpalteSummeVp.ToString() + "-" + ka.Name + "!SummeBrutto";
                } else {
                    r2.Value = Resources.CreateDifferenceErrorWithoutSummeBrutto;
                }
                r2.Font.ThemeColor = 7;
                r2.Font.Bold = true;

                Range r3 = ka.Range[ka.Cells[Settings.Default.ZeileStart, Settings.Default.SpalteText], ka.Cells[copyRow - 1, Settings.Default.SpalteSummeVp]];
                target.PageSetup.PrintArea = r3.Address;
            } catch (Exception ex) {
                log.Add("CreateDifference", ex);
            }
        }

        private static void CreateLsZeile(Range row, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectRow selRow = new ProjectRow(row);

                if (copyRow >= Settings.Default.ZeileLsMaxFirstPage) {
                    Range newRow = target.Rows[copyRow];
                    newRow.Select();
                    newRow.Copy();
                    newRow.Select();
                    newRow.Insert(Shift: XlInsertShiftDirection.xlShiftDown);
                }

                Range prLeText = target.Range[Settings.Default.SpalteLsProdukteLeistung + copyRow.ToString()];
                prLeText.Value = selRow.RowText;

                Range einheit = target.Range[Settings.Default.SpalteLsEinheit + copyRow.ToString()];
                einheit.Value = selRow.Einheit;

                Range menge = target.Range[Settings.Default.SpalteLsMenge + copyRow.ToString()];
                menge.Value = selRow.Menge;

                Range targetRow = target.Rows[copyRow];
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].LineStyle = 1;
                targetRow.Borders[XlBordersIndex.xlEdgeBottom].Weight = 1;
            } catch (Exception ex) {
                log.Add("CreateLsZeile(" + target.Name + ")", ex);
            }
        }

        public static void CreateNewCalc(Worksheet sh, Logger log) {
            TpMenu myMenu = GetTpMenu(sh, log);
            CalcMenu aWindow = new CalcMenu(myMenu);
            aWindow.ShowDialog();
            if (!aWindow.Cancel) {
                try {
                    DeleteEmptyRows(sh, aWindow.SelectedColumn, log);
                    Range r1 = sh.Cells[3, 1];
                    r1.Value = "Doc";
                } catch (Exception ex) {
                    log.Add("CreateNewCalc", ex);
                }
            }
        }

        public static void CreateRechnungsDifference(Workbook wb, Worksheet ka, Worksheet target, int copyRow, Logger log) {
            try {
                ProjectSheet sheet = new ProjectSheet(ka);
                Range r1 = target.Cells[copyRow, Settings.Default.SpalteText];
                r1.Value = "Differenz";
                r1.Font.Bold = true;
                string formel = "";
                foreach (Worksheet s1 in wb.Sheets) {
                    ProjectSheet sheet1 = new ProjectSheet(s1);
                    if (sheet1.SheetName.Contains("f") & sheet1.ProjektNummer == sheet.ProjektNummer & sheet1.SheetName != target.Name) {
                        if (sheet1.HasNames) {
                            formel += "+" + s1.Name + "!SummeBrutto";
                        }
                    }
                }

                Range r2 = target.Cells[copyRow, Settings.Default.SpalteSummeVp];
                if (sheet.HasNames) {
                    r2.FormulaR1C1 = "=R" + Convert.ToString(copyRow - 1) + "C" + Settings.Default.SpalteSummeVp.ToString() + "-" + ka.Name + "!SummeBrutto" + formel;
                } else {
                    r2.Value = Resources.CreateDifferenceErrorWithoutSummeBrutto;
                }
                r2.Font.ThemeColor = 7;
                r2.Font.Bold = true;

                Range r3 = ka.Range[ka.Cells[Settings.Default.ZeileStart, Settings.Default.SpalteText], ka.Cells[copyRow - 1, Settings.Default.SpalteSummeVp]];
                target.PageSetup.PrintArea = r3.Address;
            } catch (Exception ex) {
                log.Add("CreateRechnungsDifference(" + ka.Name + ")", ex);
            }
        }

        public static bool CreateSummenZeile(Range row, ProjectRow selRow, Worksheet ka, Worksheet target, int copyRow, int lastSummenRow, Logger log) {
            try {
                if (((selRow.RowType.ToUpper() == "SU") | (selRow.RowType == "S4")) & (selRow.IsSummenZeile)) {

                    int sHierachie = selRow.SummenHierachie;
                    Range r1 = target.Rows[copyRow];
                    Range r2 = r1.Cells[1, Settings.Default.SpalteSummeVp];

                    switch (sHierachie) {
                        case 1:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true, log);
                                r2.FormulaR1C1 = "=Sum(R" + (lastSummenRow == 0 ? GlobalVar.SummenStartRow.ToString() : (string)(lastSummenRow + 1).ToString()) + "C" + Settings.Default.SpalteSummeVp + ":R" + (string)(copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, copyRow, 1, log);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 2:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true, log);
                                r2.FormulaR1C1 = GetGesamtSummenFormel(2);
                                if (GlobalVar.ProduktZeilenAnzahl > 0) {
                                    string s1 = GetGesamtSummenFormel(1).Replace("=", "+");
                                    r2.FormulaR1C1 += s1;
                                }
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, copyRow, 2, log);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 3:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true, log);
                                r2.FormulaR1C1 = GetGesamtSummenFormel(3);

                                if (GlobalVar.ProduktZeilenAnzahl > 0) {
                                    string s1 = GetGesamtSummenFormel(1).Replace("=", "+");
                                    r2.FormulaR1C1 += s1;
                                }
                                GlobalVar.RemoveAllProduktZeilen();
                                SetGlobalSummen(copyRow, 0, 3, log);
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        case 4:
                            if (selRow.BildeSumme) {
                                CopyProduktZeile(row, target, copyRow, true, log);
                                switch (selRow.S4Type) {
                                    case "Gesamt":
                                        GlobalVar.SummeGesamtFormel = GetGesamtSummenFormel(4);
                                        r2.FormulaR1C1 = GlobalVar.SummeGesamtFormel;
                                        GlobalVar.S4SummenStart = copyRow;
                                        break;
                                    case "Summe netto":

                                        r2.FormulaR1C1 = "=Sum(R" + GlobalVar.S4SummenStart.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ":R" + ((int)copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                        target.Names.Add("SummeNetto", r2);
                                        if (!GlobalVar.CopyRabatt) {
                                            copyRow--;
                                            Range rowBrutto = target.Rows[copyRow];
                                            rowBrutto.Delete();
                                            r2.FormulaR1C1 = GlobalVar.SummeGesamtFormel;
                                            SetSummenZeile(target, copyRow);
                                            return false;
                                        }

                                        break;
                                    case "Summe brutto":
                                        r2.FormulaR1C1 = "=Sum(R" + ((int)copyRow - 2).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ":R" + ((int)copyRow - 1).ToString() + "C" + Settings.Default.SpalteSummeVp.ToString() + ")";
                                        GlobalVar.RemoveSummenAddress(4, true);
                                        target.Names.Add("SummeBrutto", r2);
                                        break;
                                }
                                SetSummenZeile(target, copyRow);
                                return true;
                            }
                            return false;
                        default:
                            break;

                    }
                }
            } catch (Exception ex) {
                log.Add(ka.Name + ", " + "CreateSummenZeile(" + selRow.RowText + ")", ex);
            }
            return false;
        }

        private static void DeleteCustomsProperty(Worksheet sh, string propertyName) {
            foreach (CustomProperty prop in sh.CustomProperties) {
                if (prop.Name == propertyName) {
                    prop.Delete();
                }
            }
        }

        private static void DeleteMenuEntries(Microsoft.Office.Interop.Excel.Application exApp) {
            DeleteMenuEntries(exApp, null);
        }

        private static void DeleteMenuEntries(Microsoft.Office.Interop.Excel.Application exApp, string menuName) {
            if (menuName == null) {
                foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Delete();
                    }
                }
            } else {
                foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                    if (me.Caption == menuName) {
                        if (KontextMenuControlExists(exApp, me.Caption)) {
                            exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Delete();
                        }
                    }
                }
            }
        }

        private static void EnableDisableKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Worksheet sh) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (KontextMenuControlExists(exApp, me.Caption)) {
                    exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = false;
                }
            }
            GlobalVar.RemoveAllMenuGroups();

            ProjectSheet sheet = new ProjectSheet(sh);
            SheetType selType = (SheetType)sheet.SheetType;
            switch (selType) {
                case SheetType.Kalkulation:
                    EnableDisableKontextMenu(exApp, SheetType.Kalkulation.GetStringValue());
                    if (sheet.CalcType == "K") {
                        EnableDisableMenuEntry(exApp, SheetType.Auftragsbestaetigung.GetStringValue(), GlobalVar.tpContextMenuName, false);
                    }
                    break;
                case SheetType.Angebot:
                    EnableDisableKontextMenu(exApp, SheetType.Angebot.GetStringValue());
                    break;
                case SheetType.Auftragsbestaetigung:
                    EnableDisableKontextMenu(exApp, SheetType.Auftragsbestaetigung.GetStringValue());
                    break;
                case SheetType.Rechnung:
                    EnableDisableKontextMenu(exApp, SheetType.Rechnung.GetStringValue());
                    break;
                case SheetType.Projektbericht:
                    EnableDisableKontextMenu(exApp, SheetType.Projektbericht.GetStringValue());
                    break;
                case SheetType.Abnahmeprotokoll:
                    EnableDisableKontextMenu(exApp, SheetType.Abnahmeprotokoll.GetStringValue());
                    break;
                case SheetType.Abrechnung:
                    EnableDisableKontextMenu(exApp, SheetType.Abrechnung.GetStringValue());
                    break;
                case SheetType.Lieferschein:
                    EnableDisableKontextMenu(exApp, SheetType.Lieferschein.GetStringValue());
                    break;
                default:
                    EnableDisableKontextMenu(exApp, "EverySheet");
                    break;
            }
        }

        private static void EnableDisableKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, string menuName) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (me.Enabled) {
                    Type typeMenu = me.GetType();
                    foreach (PropertyInfo pInfo in typeMenu.GetProperties()) {
                        if ((pInfo.Name == menuName) | (pInfo.Name == SheetType.EverySheet.GetStringValue())) {
                            bool setValue = false;
                            setValue = (bool)pInfo.GetValue(me, null);
                            if (setValue) {
                                if (KontextMenuControlExists(exApp, me.Caption)) {
                                    exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = true;
                                    if (!me.GroupHeader) {
                                        GlobalVar.AddMenuGroups(me.GroupID);
                                    }
                                }
                            }
                        }
                    }
                } else {
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = false;
                    }
                }
            }
        }

        private static void EnableDisableMenuGroups(Microsoft.Office.Interop.Excel.Application exApp) {
            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                if (me.GroupHeader) {
                    bool setValue = GlobalVar.EnableMenuGroup(me.GroupID);
                    if (KontextMenuControlExists(exApp, me.Caption)) {
                        exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[me.Caption].Enabled = setValue;
                    }
                }
            }
        }

        private static void EnableDisableMenuEntry(Microsoft.Office.Interop.Excel.Application exApp, string caption, string commandBarName, bool enable) {
            exApp.CommandBars[commandBarName].Controls[caption].Enabled = enable;
        }

        private static void FillWordDocumentFields(myOffice.Word.Application appWord, myOffice.Word.Document myDoc, string selType, Recipient myRecipient, Logger log) {
            FillWordDocumentFields(appWord, myDoc, selType, myRecipient, log, null);
        }

        private static void FillWordDocumentFields(myOffice.Word.Application appWord, myOffice.Word.Document myDoc, string selType, Recipient myRecipient, Logger log, InvoiceObject inv) {
            try {
                Dictionary<string, string> myDict = new Dictionary<string, string>();
                AddDictionayFields(myDict, "Recipient", myRecipient.Name);
                AddDictionayFields(myDict, "zuHanden", (myRecipient.CheckZuHanden ? Resources.zuHanden.AddSpace() + myRecipient.ZuHanden : ""));
                AddDictionayFields(myDict, "Straße", myRecipient.Straße);
                AddDictionayFields(myDict, "PlzOrt", myRecipient.PlzOrt);
                AddDictionayFields(myDict, "Anrede", myRecipient.Anrede);
                AddDictionayFields(myDict, "eMail", myRecipient.EMail);
                AddDictionayFields(myDict, "FaxNr", myRecipient.Fax);
                AddDictionayFields(myDict, "Empfänger", myRecipient.Name);

                AddDictionayFields(myDict, "US", "us" + System.Environment.UserName.ToLower());
                AddDictionayFields(myDict, "VeranlasserKZ", System.Environment.UserName);
                AddDictionayFields(myDict, "VeranlasserIn", "in" + System.Environment.UserName.ToLower());
                AddDictionayFields(myDict, "Veranlasser", UserPrincipal.Current.DisplayName);
                AddDictionayFields(myDict, "BearbeiterKZ", Resources.Ma);
                AddDictionayFields(myDict, "Datum", DateTime.Today.ToString(Resources.TpDatumsFormat));
                AddDictionayFields(myDict, "DateiName", (myDoc != null ? Tools.Left(myDoc.Name, myDoc.Name.IndexOf(".")).Replace(GlobalVar.currentOrganisation.OrgName, TpKuerzel.Verk.GetStringValue() + @"\") : ""));
                AddDictionayFields(myDict, "DatumBis", GetDatumBis().ToString(Resources.TpDatumsFormat));
                AddDictionayFields(myDict, "Betreff", myRecipient.Betreff);
                AddDictionayFields(myDict, "Thema", myRecipient.Betreff);

                if (inv != null) {
                    StringBuilder myText = new StringBuilder();
                    myText.Append(inv.ZahlungsInfo);

                    log.Add("Start Inv-AddDictionayFields");
                    bool empf = (inv.ReEmpfaenger != null ? true : false);
                    AddDictionayFields(myDict, "invEmpfänger", (empf ? inv.ReEmpfaenger : inv.Auftraggeber));
                    AddDictionayFields(myDict, "zHdn", "");
                    if (empf & inv.RePersonCheck) {
                        myDict.Remove("zHdn");
                        AddDictionayFields(myDict, "zHdn", inv.RePerson);
                    }
                    if (!empf & inv.CheckAuftraggeberPerson) {
                        myDict.Remove("zHdn");
                        AddDictionayFields(myDict, "zHdn", inv.AuftraggeberHdn);
                    }
                    AddDictionayFields(myDict, "invStrasse", (empf ? inv.ReStrasse : inv.AuftraggeberStrasse));
                    AddDictionayFields(myDict, "invPlzOrt", (empf ? inv.RePlzOrt : inv.AuftraggeberPlzOrt));
                    AddDictionayFields(myDict, "invMa", inv.Ma);
                    AddDictionayFields(myDict, "invReNr", inv.Rechnungsdatum.Year.ToString().Right(2) + inv.RechnungsNummer.ToString("000"));
                    AddDictionayFields(myDict, "invDatum", inv.Rechnungsdatum.ToString(Extensions.tpDateFormat));
                    if (inv.AFAbDatum.Value.Year > 1900) {
                        DateTime dt = (DateTime)inv.AFAbDatum;
                        AddDictionayFields(myDict, "AuftragsDatum", String.Format(Resources.tmAuftragBestaetigung, dt.ToString(Extensions.tpDateFormat)));
                    } else {
                        AddDictionayFields(myDict, "AuftragsDatum", inv.AuftragsDatum.ToString(Extensions.tpDateFormat));

                    }
                    if (inv.AFDurchfuehrungBis.Value.Day != inv.AFDurchfuehrungVon.Value.Day) {
                        DateTime dVon = (DateTime)inv.AFDurchfuehrungVon;
                        DateTime dBis = (DateTime)inv.AFDurchfuehrungBis;
                        AddDictionayFields(myDict, "ArbeitsDatum", dVon.ToString(Extensions.tpDateFormat) + " bis " + dBis.ToString(Extensions.tpDateFormat));
                    } else {
                        AddDictionayFields(myDict, "ArbeitsDatum", inv.ArbeitsDatum.ToString(Extensions.tpDateFormat));
                    }
                    AddDictionayFields(myDict, "ProjektName", inv.Kunde);
                    AddDictionayFields(myDict, "Projekt", inv.ProjektBezeichnung);
                    AddDictionayFields(myDict, "PersKonto", inv.PersonenKonto);
                    AddDictionayFields(myDict, "SachKonto", inv.SachKonto);
                    AddDictionayFields(myDict, "Beilage", inv.Beilage);



                    if (inv.IsBusiness) {
                        if (inv.Ust == 0.0) {
                            myText.Append("\nUSt:\t");
                            myText.AppendLine(Resources.tmUstBauwesen);
                        }
                        if (inv.UID != null) {
                            myText.Append("\nUID:\t");
                            myText.Append(inv.UID);
                        }
                    }

                    if (inv.BeilageCheckLS | inv.BeilageCheckAP | inv.BeilageDateiCheck | inv.BeilageZusatzTextCheck | inv.Zahlschein) {
                        myText.Append("\nBeilage:\t");
                        myText.Append(inv.Beilage);
                    }

                    AddDictionayFields(myDict, "EmpfSeite2", (inv.ReEmpfaenger == null ? inv.Auftraggeber : inv.ReEmpfaenger));
                    AddDictionayFields(myDict, "ReNrSeite2", inv.Rechnungsdatum.Year.ToString().Right(2) + inv.RechnungsNummer.ToString("000"));

                    AddDictionayFields(myDict, "ZahlungsInfo", myText.ToString());

                    log.Add("End Inv-AddDictionayFields");
                }

                SetWordBookmarks(myDoc, myDict, log);

                if (inv != null) {
                    myOffice.Word.Range myWordRange = myDoc.Range();
                    object toFind = "USt:";
                    if (myWordRange.Find.Execute(FindText: toFind, MatchWholeWord: true)) {
                        myWordRange.Font.Bold = 1;
                    }
                    toFind = "UID:";
                    myWordRange = myDoc.Range();
                    if (myWordRange.Find.Execute(FindText: toFind, MatchWholeWord: true)) {
                        myWordRange.Font.Bold = 1;
                    }
                    toFind = "Beilage:";
                    myWordRange = myDoc.Range();
                    if (myWordRange.Find.Execute(FindText: toFind, MatchWholeWord: true)) {
                        myWordRange.Font.Bold = 1;
                    }
                } else {

                    myOffice.Word.Range myRange = myDoc.Range();
                    object findText = "us" + System.Environment.UserName.ToLower();
                    if (myRange.Find.Execute(FindText: findText, MatchWholeWord: true)) {
                        myRange.InsertAutoText();
                    }

                    myRange = myDoc.Range();
                    findText = "in" + System.Environment.UserName.ToLower();
                    if (myRange.Find.Execute(FindText: findText, MatchWholeWord: true)) {
                        myRange.InsertAutoText();
                    }
                }
            } catch (Exception e) {
                log.Add("FillWordDocumentFields", e);
            }
        }

        public static string GetGesamtSummenFormel(byte type) {
            string ret = "=";
            switch (type) {
                case 1:
                    foreach (string s1 in GlobalVar.ProduktZeilen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 2:
                    foreach (string s1 in GlobalVar.S2SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 3:
                    if (GlobalVar.S3SummenZellen.Count == 0) {
                        goto case 2;
                    }
                    foreach (string s1 in GlobalVar.S3SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    break;
                case 4:
                    foreach (string s1 in GlobalVar.SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    foreach (string s1 in GlobalVar.S3SummenZellen) {
                        ret += (ret.Length > 1 ? "+" : "") + s1;
                    }
                    if (GlobalVar.S3SummenZellen.Count == 0) {
                        foreach (string s1 in GlobalVar.S2SummenZellen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }
                    if (GlobalVar.S2SummenZellen.Count == 0) {
                        foreach (string s1 in GlobalVar.S1SummenZellen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }
                    if (GlobalVar.ProduktZeilenAnzahl > 0) {
                        foreach (string s1 in GlobalVar.ProduktZeilen) {
                            ret += (ret.Length > 1 ? "+" : "") + s1;
                        }
                    }

                    break;
            }
            return ret;
        }

        public static void KontextMenuAddControl(Microsoft.Office.Interop.Excel.Application exApp, Logger log) {

            foreach (MenuEintrag me in GlobalVar.MenuEntries) {
                try {
                    if (!KontextMenuControlExists(exApp, me.Caption)) {
                        CommandBarButton cbb = (CommandBarButton)exApp.CommandBars[GlobalVar.tpContextMenuName].Controls.Add();
                        cbb.Caption = me.Caption;
                        cbb.OnAction = me.OnAction;
                        cbb.FaceId = me.FaceID;
                        cbb.BeginGroup = me.NewGroup;
                        cbb.Enabled = me.Enabled;
                        cbb.Height = 0;
                    }
                } catch (Exception ex) {
                    log.Add("KontextMenuAddControl(" + me.Caption + ")", ex);
                }
            }
        }

        private static bool KontextMenuControlExists(Microsoft.Office.Interop.Excel.Application exApp, string caption) {
            foreach (CommandBarButton cbb in exApp.CommandBars[GlobalVar.tpContextMenuName].Controls) {
                if (cbb.Caption == caption) {
                    return true;
                }
            }
            return false;
        }

        public static void KontextMenuRemoveControl(Microsoft.Office.Interop.Excel.Application exApp, bool delete, Logger log) {
            try {
                if (delete) {
                    foreach (GlobalVar.MenuEntry menu in GlobalVar.Elements) {
                        if (CBControlExists(exApp.CommandBars[GlobalVar.tpContextMenuName], menu.Caption)) {
                            exApp.CommandBars[GlobalVar.tpContextMenuName].Controls[menu.Caption].Delete();
                        }
                    }
                    return;
                }
            } catch (Exception ex) {
                log.Add("KontextMenuRemoveControl", ex);
            }
        }

        public static void SetCalcMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            string t1 = "";
            Range r1 = sh.Cells[1, 1];
            if (r1.Value != null) {
                t1 = Convert.ToString(r1.Value);
            }

            if (t1.Contains("Ka")) {
                r1 = sh.Cells[3, 1];
                if (r1.Value == null & wb.Sheets.Count < 2 & wb.FileFormat == XlFileFormat.xlOpenXMLWorkbookMacroEnabled) {
                    CreateNewCalc(sh, log);
                }
            }
        }

        private static void SetDataAbnahmeprotokoll(Organisation org, Worksheet ap, ProjectSheet sheet, bool isRep, Logger log) {
            try {
                Range apName = (isRep ? ap.Range[Settings.Default.ZelleApRepName] : ap.Range[Settings.Default.ZelleApName]);
                apName.Value = org.Prefix.AddSpace() + org.OrgName + " " + org.ZusätzeFW.AddSpace() + org.RechtsForm;

                Range apDateiSuchbegriff = (isRep ? ap.Range[Settings.Default.ZelleApRepDateiSuchbegriff] : ap.Range[Settings.Default.ZelleApDateiSuchbegriff]);
                apDateiSuchbegriff.Value = org.Suchbegriff;

                Range apDateiAbteilung = (isRep ? ap.Range[Settings.Default.ZelleApRepDateiAbteilung] : ap.Range[Settings.Default.ZelleApDateiAbteilung]);
                apDateiAbteilung.Value = org.Abteilung;

                Range apAdresse = (isRep ? ap.Range[Settings.Default.ZelleApRepAdresse] : ap.Range[Settings.Default.ZelleApAdresse]);
                apAdresse.Value = org.Plz.AddSpace() + org.Ort + ", " + org.Straße;
            } catch (Exception ex) {
                log.Add("SetDataAbnahmeprotokoll", ex);
            }
        }

        public static void SetDataProjektBericht(Organisation org, Worksheet pb, ProjectSheet sheet, Logger log) {
            try {
                if (org != null) {
                    //Kunde
                    Range r1 = pb.Range[Settings.Default.ProjektberichtZelleKunde];
                    r1.Value = org.OrgName;

                    //Suchbegriff
                    r1 = pb.Range[Settings.Default.ProjektberichtZelleDateiJahr];
                    r1.Value = org.Suchbegriff;

                    //Projektnummer
                    r1 = pb.Range[Settings.Default.ProjektberichtZelleDateiProjekt];
                    r1.Value = "P" + sheet.ProjektNummer.ToString();

                    //Adresse
                    r1 = pb.Range[Settings.Default.ProjektberichtZelleAdresse];
                    r1.Value = org.Plz + " " + org.Ort + ", " + org.Straße;

                    //Telefon Festnetz
                    r1 = pb.Range[Settings.Default.ProjektberichtZelleTelFestnetz];
                    r1.Value = org.TelNr1;

                    //Telefon Mobil
                    r1 = pb.Range[Settings.Default.ProjektberichtZelleTelMobil];
                    r1.Value = org.TelNr2;
                }
            } catch (Exception ex) {
                log.Add("SetDataProjektBericht", ex);
            }
        }

        public static void SetGlobalSummen(int copyRow, int lastSummenRow, int hierachie, Logger log) {
            try {
                string s1 = "R" + copyRow.ToString() + "C" + Settings.Default.SpalteSummeVp.ToString();
                GlobalVar.LastSummenRow = lastSummenRow;
                GlobalVar.MaxSummenHierachie = hierachie;
                GlobalVar.SummenStartRow = copyRow + 1;
                GlobalVar.PreviousSummenHierachie = hierachie;
                GlobalVar.AddSummenAddress(1, s1);

                if (hierachie == 1) {
                    GlobalVar.AddSummenAddress(2, s1);
                }

                if (hierachie == 2) {
                    GlobalVar.SummenStartRow2 = copyRow + 1;
                    GlobalVar.AddSummenAddress(3, s1);
                    GlobalVar.RemoveSummenAddress(1, true);
                    GlobalVar.RemoveSummenAddress(2, true);
                }

                if (hierachie == 3) {
                    GlobalVar.SummenStartRow2 = copyRow + 1;
                    GlobalVar.SummenStartRow3 = copyRow + 1;
                    GlobalVar.PreviousSummenHierachie = 0;
                    GlobalVar.AddSummenAddress(4, s1);
                    GlobalVar.RemoveSummenAddress(1, true);
                    GlobalVar.RemoveSummenAddress(2, true);
                    GlobalVar.RemoveSummenAddress(3, true);
                }
            } catch (Exception ex) {
                log.Add("SetGlobalSummen", ex);
            }
        }

        public static void SetKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, Logger log) {
            SetKontextMenu(exApp, wb, sh, false, log);
        }

        public static void SetKontextMenu(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh, bool delete, Logger log) {
            try {
                if (wb.FileFormat != XlFileFormat.xlOpenXMLWorkbookMacroEnabled & wb.FileFormat != XlFileFormat.xlOpenXMLTemplateMacroEnabled) {
                    DeleteMenuEntries(exApp);
                    return;
                }

                AddMenuEntries(log);

                KontextMenuAddControl(exApp, log);

                EnableDisableKontextMenu(exApp, sh);

                EnableDisableMenuGroups(exApp);

            } catch (Exception ex) {
                log.Add("SetKontextMenu:" + wb.Name, ex);
            }
        }

        private static void SetDataLieferschein(Organisation org, Worksheet ap, Logger log) {
            try {
                Range apName = ap.Range[Settings.Default.ZelleLsName];
                apName.Value = org.Prefix.AddSpace() + org.OrgName + " " + org.ZusätzeFW.AddSpace() + org.RechtsForm;

                Range apDateiSuchbegriff = ap.Range[Settings.Default.ZelleLsDateiSuchbegriff];
                apDateiSuchbegriff.Value = org.Suchbegriff;

                Range apDateiAbteilung = ap.Range[Settings.Default.ZelleLsDateiAbteilung];
                apDateiAbteilung.Value = org.Abteilung;

                Range apAdresse = ap.Range[Settings.Default.ZelleLsAdresse];
                apAdresse.Value = org.Plz.AddSpace() + org.Ort + ", " + org.Straße;

                Range datum = ap.Range[Settings.Default.ZelleLsDatum];
                datum.Value = Tools.GetTpDateToday();
            } catch (Exception ex) {
                log.Add("SetDataLieferschein", ex);
            }
        }

        private static void SetSpecialRowSettings(string sourceFullname, Range targetRows) {
            string shortCode = (sourceFullname.Contains(@"\Kund\") ? "Kund" : (sourceFullname.Contains(@"\Verk\") ? "Verk" : "Verk"));
            Range r2 = targetRows.Cells[1, "E"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "F"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "G"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "H"];
            r2.Value = shortCode;
            r2 = targetRows.Cells[1, "W"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "X"];
            r2.Value = "";
            r2 = targetRows.Cells[1, "BA"];
            r2.Value = sourceFullname;
        }

        public static void SetSummenZeile(Worksheet sh, int row) {
            ((Range)sh.Rows[row]).Font.Bold = true;
        }

        public static void DeleteEmptyRows(Worksheet sh, int col, Logger log) {
            try {
                int startRow = 301;
                int nextRow;
                Range rLast = sh.get_Range("LastRow");
                Range r1 = sh.Cells[startRow, col];
                int lastRow = rLast.Row;
                object toFind = null;
                Range rCol = sh.Columns[col];

                for (int i = startRow; startRow < lastRow; i++) {
                    r1 = sh.Cells[i, col];
                    if (r1.Value == null) {
                        nextRow = i;
                    } else {
                        nextRow = GetNextRowNumber(sh, rCol, r1, toFind);
                    }
                    if (nextRow < i) {
                        return;
                    }
                    if (toFind == null) {
                        Range r2 = sh.Cells[nextRow, col];
                        toFind = "1";
                        int delRow = GetNextRowNumber(sh, rCol, r2, toFind);
                        if (delRow < i) {
                            return;
                        }
                        Range delRange = sh.Rows[nextRow.ToString() + ":" + (delRow - 1).ToString()];
                        delRange.Delete();
                        i = nextRow;
                        lastRow = lastRow - (delRow - nextRow);
                        toFind = null;
                    }
                }
            } catch (Exception ex) {
                log.Add("DeleteEmptyRows", ex);
            }
        }

        public static void SetWordBookmarks(myOffice.Word.Document myDoc, Dictionary<string, string> myDict, Logger log) {
            try {
                foreach (myOffice.Word.Bookmark bm in myDoc.Bookmarks) {
                    string tmName = bm.Name;
                    if (myDict.ContainsKey(tmName)) {
                        bm.Range.Text = myDict[tmName];
                    }
                }
            } catch (Exception ex) {
                log.Add("SetWordBookmarks", ex);
            }
        }

        public static void TestRoutine(Microsoft.Office.Interop.Excel.Application exApp, Workbook wb, Worksheet sh) {
            Range row1 = sh.Rows[322];
            ProjectRow row = new ProjectRow(row1);

            Range mr1 = row1.Cells[1, Settings.Default.SpalteZeilenArt];

            string ts1 = mr1.Value;

            string s1 = row.RowType;
            string r2 = row.RowTerm;
            string r3 = row.RowText;
            bool b1 = row.IsVpRow;
            bool b2 = row.IsEpRow;

            double d1 = row.SummeEp;
            double d2 = row.SummeVp;

        }

        public static void UpdateAeDetails(ProjectSheet mySheet, AeRow myRow, Logger log) {
            try {
                #region Projekt Adresse

                if (mySheet.AuftragsDatum != new DateTime(1900, 1, 1)) {
                    myRow.AuftragsDatum = mySheet.AuftragsDatum;
                }
                myRow.ProjektName = mySheet.Kundenname;
                myRow.ProjektBezeichnung = mySheet.Projekt;
                myRow.Plz = mySheet.Plz.ToString();
                myRow.Ort = mySheet.Ort;
                myRow.Straße = mySheet.Straße;

                #endregion

                #region Erlös

                myRow.SummeHandelsware = mySheet.AeSummeHandelsware;
                myRow.SummeMontage = mySheet.AeSummeMontage;
                myRow.SummeProduktion = mySheet.AeSummeProduktion;
                myRow.RabattGesamt = mySheet.AeRabattProzent + mySheet.AeRabattFix + mySheet.AeZusatzRabattProzent;
                myRow.RabattLeistungenGesamt = mySheet.AeRabattLeistungenProzent + mySheet.AeRabattLeistungenFix;
                myRow.RabattProdukteGesamt = mySheet.AeRabattProdukteProzent + mySheet.AeRabattProdukteFix;
                myRow.Erlösminderung = mySheet.AeErlösMinderung;
                myRow.Forderungsausfall = mySheet.AeForderungsausfall;
                myRow.Schadenfall = mySheet.AeSchadenfall;

                #endregion

                #region Kosten

                myRow.KostenAdministration = mySheet.AeKostenSummeAdministration;
                myRow.KostenHandelsware = mySheet.AeKostenSummeHandelsware;
                myRow.KostenMontage = mySheet.AeKostenSummeMontage;
                myRow.KostenProduktion = mySheet.AeKostenSummeProduktion;

                #endregion

                #region Zusatzkosten

                myRow.ZkBaustellenInfrastrukturFix = mySheet.AeZusatzKostenBaustelleninfrastrukturFix;
                myRow.ZkBaustellenInfrastrukturProzent = mySheet.AeZusatzKostenBaustelleninfrastrukturProzent;
                myRow.ZkErfüllungsgarantie = mySheet.AeZusatzkostenErfüllungsgarantie;
                myRow.ZkHaftrücklass = mySheet.AeZusatzkostenHaftrücklass;
                myRow.ZkProvisionDB = mySheet.AeZusatzkostenProvsionDB;
                myRow.ZkProvisionErlösFix = mySheet.AeZusatzKostenProvisionsErlösFix;
                myRow.ZkProvisionErlösProzent = mySheet.AeZusatzKostenProvisionsErlösProzent;
                myRow.ZkSkonto = mySheet.AeZusatzkostenSkonto;

                #endregion

                #region Zeitaufwand

                myRow.ZeitaufwandAdministration = mySheet.AeZeitaufwandAdminsitration;
                myRow.ZeitaufwandMontage = mySheet.AeZeitaufwandMontage;
                myRow.ZeitaufwandProduktion = mySheet.AeZeitaufwandProdutktion + mySheet.AeZeitaufwandProdukte;

                #endregion
            } catch (Exception ex) {
                log.Add("UpdateAepüDetails", ex);
            }
        }

        public static void Write2Liqui(InvoiceObject i, bool oldRow, Microsoft.Office.Interop.Excel.Application exApp, Logger log) {
            Workbook wb = null;
            try {
                log.Add("Start Write2Liqui");
                bool ze = (i.ZahlArt == TpBaseObjects.Global.InvoicePayType.NachRechnungserhalt);
                string fileName = Path.Combine(Settings.Default.PathRewe + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + @"\Liqui.xlsm");
                string sheetName = (ze ? "Ze" : "Iktp");
                //Testen
                //fileName = @"T:\Rewe\12\LiquiTestEs.xlsm";

                if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                    log.Add("Datei " + fileName + " ist geöffnet oder nicht vorhanden!");
                    return;
                }

                wb = exApp.Workbooks.Open(fileName);

                if (!Tools.SheetsExists(wb, sheetName)) {
                    return;
                }

                Worksheet sh = wb.Sheets[sheetName];
                ProjektLiquiSheet liqui = new ProjektLiquiSheet(sh);
                int rowNumber = 0;
                if (oldRow) {
                    Range rCol = sh.Columns["D"];
                    Range aCell = sh.Cells[1, "D"];
                    object toFind = i.RechnungsNummer;
                    rowNumber = GetNextRowNumber(sh, rCol, aCell, toFind, true);
                } else {
                    rowNumber = liqui.GetNextFreeRowNumber();
                }

                if (rowNumber == 0) {
                    log.Add("roldRow: " + oldRow.ToString() + " rowNumber=0");
                    return;
                }

                log.Add("Write to Row: " + rowNumber.ToString());
                Range newRow = sh.Rows[rowNumber];
                ProjektLiquiRow lRow = new ProjektLiquiRow(newRow, ze);

                lRow.RechnungsNummer = i.RechnungsNummer;
                lRow.RechnungsDatum = i.Rechnungsdatum;
                lRow.Kunde = i.Kunde + i.ProjektBezeichnung;
                lRow.Rechnungsempfänger = i.ReEmpfaenger;
                lRow.DebitorenKonto = i.PersonenKonto;
                lRow.SummeBrutto = i.RechnungsBetrag - (double)(i.SkontoBetrag != null ? i.SkontoBetrag : 0);

                lRow.Datum = Tools.NextGoodBusinessDay(DateTime.Now);
                lRow.Firma = "Tp";
                lRow.Type = "Ar";
                if (ze) {
                    lRow.Datum = GetNextDueDate(i.Rechnungsdatum);
                    lRow.DiktatZeichen = i.Ma;
                }
                log.Add("End Write2Liqui");
            } catch (Exception ex) {
                log.Add("Write2Liqui", ex);
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close();
                }
            }
        }

        #endregion

        #region Functions

        private static bool CBControlExists(CommandBar cb, string cbName) {
            foreach (CommandBarControl ctl in cb.Controls) {
                if (ctl.Caption == cbName) {
                    return true;
                }
            }
            return false;
        }

        private static bool CheckCalcSheets(Workbook wb, Worksheet sh) {
            string shName = string.Empty;

            #region Datenfelder

            #region Fehlt

            bool ret = false;
            ProjectSheet sheet = new ProjectSheet(sh);
            if (sheet.IsCalcSheet) {
                shName = sh.Name;
                string selCell = "";
                string msg = Resources.CheckCalcSheetsAskMissing + "\n\r\n\r";
                string msgFalsch = Resources.CheckCalcSheetsAskFalse + "\n\r\n\r";
                int lenMsg = msg.Length;

                //Kundenname
                if (sheet.Kundenname == "") {
                    selCell = Settings.Default.ZelleKundenname;
                    msg += Resources.Kundenname + "\n\r";
                }

                //Projekt
                if (sheet.Projekt == "") {
                    selCell = Settings.Default.ZelleProjektbezeichnung;
                    msg += Resources.Projektbezeichnung + "\n\r";
                }

                //Plz
                if (sheet.Plz == 0) {
                    selCell = Settings.Default.ZellePlz;
                    msg += Resources.Plz + "\n\r";
                }

                //Ort
                if (sheet.Ort == "") {
                    selCell = Settings.Default.ZelleOrt;
                    msg += Resources.Ort + "\n\r";
                }

                //Straße
                if (sheet.Straße == "") {
                    selCell = Settings.Default.ZelleStrasse;
                    msg += Resources.Straße + "\n\r";
                }

                //Kundengruppe
                if (sheet.Kundengruppe == 0) {
                    selCell = Settings.Default.ZelleKundengruppe;
                    msg += Resources.Kundengruppe + "\n\r";
                } else {
                    if ((sheet.Kundengruppe < 1) | (sheet.Kundengruppe > 5)) {
                        selCell = Settings.Default.ZelleKundengruppe;
                        msgFalsch += Resources.Kundengruppe + " (zulässiger Wert = 1 bis 5)\n\r";
                    }
                }

                //Kilometer
                if (sheet.Kilometer == 0) {
                    selCell = Settings.Default.ZelleKilometer;
                    msg += Resources.Kilometer + "\n\r";
                }

                if (msg.Length != lenMsg) {
                    msg += (msgFalsch.Length > 23 ? "\n\r" + msgFalsch : "");
                    if (MessageBox.Show(msg + "\n\r" + Resources.OhneEingabeSpeichern, shName, MessageBoxButtons.YesNo, MessageBoxIcon.Exclamation) == DialogResult.Yes) {
                        ret = true;
                    } else {
                        Range s1 = sh.Range[selCell];
                        sh.Activate();
                        s1.Select();
                        ret = false;
                    }
                }

            }

            #endregion

            #region Falsch




            string selCellF = "";
            string msgF = Resources.CheckCalcSheetsAskMissing + "\n\r\n\r";
            int lenMsgF = msgF.Length;

            //Kundengruppe
            if ((sheet.Kundengruppe < 1) | (sheet.Kundengruppe > 5)) {
                selCellF = Settings.Default.ZelleKundengruppe;
                msgF += Resources.Kundengruppe + Resources.zulaessigerWert1_5 + "\n\r";
                ret = false;
            }

            #endregion

            #endregion

            return ret;
        }

        public static bool CheckKilometer(Worksheet sh, bool cancel) {
            ProjectSheet sheet = new ProjectSheet(sh);

            if (sheet.PlzExists) {
                if (Math.Abs(sheet.KilometerDifferenz) > 10) {
                    string msg = String.Format(Resources.CheckKilometerMsg, (sheet.KilometerDifferenz < 0 ? "" : "+") + sheet.KilometerDifferenz.ToString());
                    MessageBox.Show(msg, sh.Name, MessageBoxButtons.OK, MessageBoxIcon.Information);
                    return true;
                }
            }
            return cancel;
        }

        public static bool CheckLiquiIsOpen(string jj) {
            bool ret = false;
            string fileName = Path.Combine(Settings.Default.PathRewe + jj, "Liqui.xlsm");
            if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                ret = true;
            }
            return ret;
        }

        public static bool CheckOrgData(Organisation org, string kundenName, string plz, string wbName, string projekt, Logger log) {
            if (org == null) {
                org = OrganisationDataManager.LoadOrganisation(kundenName, plz);
                if (org != null) {
                    string msg = String.Format(Resources.AuswahlNameInDb, kundenName, projekt, org.OrgName, org.Suchbegriff, org.Plz, org.Ort, org.Straße);
                    if (MessageBox.Show(msg, wbName, MessageBoxButtons.YesNo, MessageBoxIcon.Error) == DialogResult.No) {
                        log.Abort = true;
                        return false;
                    }
                } else {
                    string msg = String.Format(Resources.NameInDbNichtGefunden, kundenName, projekt);
                    log.Add(msg);
                    MessageBox.Show(msg, wbName, MessageBoxButtons.OK, MessageBoxIcon.Error);
                    log.Abort = true;
                    return false;
                }
            }
            return true;
        }

        private static string ChangeDokNumber(string dokName) {
            string ret = dokName;
            string ext = dokName.Substring(dokName.IndexOf("."));
            string dName = dokName.Substring(0, dokName.IndexOf("."));
            string dokNr = dName.Right(2);
            if (Tools.isNumeric(dokNr, System.Globalization.NumberStyles.Any)) {
                int dokNo = Convert.ToInt32(dName.Right(2));

                dokNo++;

                ret = dName.Substring(0, dName.Length - 2) + dokNo.ToString("00");
                ret += ext;
            }
            return ret;
        }

        public static int CountSameSheets(Workbook wb, string sheetName) {
            int lenSheetName = sheetName.Length;
            int anzSheetName = 0;
            foreach (Worksheet s in wb.Sheets) {
                if (Tools.Left(s.Name, lenSheetName) == sheetName) {
                    anzSheetName += 1;
                }
            }
            return anzSheetName;
        }

        public static int CountSheetsExists(Workbook wb, string sheetName) {
            List<string> existsSheets = new List<string>();
            foreach (Worksheet sh in wb.Sheets) {
                if (sh.Name.Contains(sheetName)) {
                    existsSheets.Add(sh.Name);
                }
            }
            return existsSheets.Count;
        }

        private static string CreateFileName(string orgName, string projekt, DocType docType, Customer customer, DocExt docExt, string projectNo) {
            return CreateFileName(orgName, projekt, docType, customer, docExt, projectNo, 0, DateTime.Now.Year.ToString().Right(2));
        }

        private static string CreateFileName(string orgName, string projekt, DocType docType, Customer customer, DocExt docExt, string projectNo, int rechnungsNummer, string jahr) {
            string fileName = string.Empty;
            if (customer == Customer.Verkauf) {
                string chkLetter = orgName.Left(1);
                string pathName = "";
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathVerkauf + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathVerkauf + @"A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathVerkauf + Tools.Left(orgName, 2) + @"\";
                }
                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + DocType.Verkauf.GetStringValue() + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Kunde) {
                string chkLetter = orgName.Left(1);
                string pathName = "";
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathKunden + jahr + @"\" + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathKunden + jahr + @"\A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathKunden + @"\" + Tools.Left(orgName, 2) + @"\";
                }
                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + DocType.Durchführung.GetStringValue() + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Verloren) {
                string pathName = Settings.Default.PathVerloren + Tools.Left(orgName, 1) + @"\";
                string chkLetter = orgName.Left(1);
                if (!Tools.isNumeric(chkLetter, System.Globalization.NumberStyles.Any)) {
                    pathName = Settings.Default.PathVerloren + jahr + @"\" + Tools.Left(orgName, 1) + @"\";
                } else {
                    pathName = Settings.Default.PathVerloren + jahr + @"\A\";
                }
                if ((orgName.Left(2) == "Wh" | orgName.Left(2) == "Wg") & orgName.Length > 3) {
                    pathName = Settings.Default.PathVerloren + jahr + @"\" + Tools.Left(orgName, 2) + @"\";
                }

                fileName = Path.Combine(pathName, orgName + Tools.Left(projekt, projekt.IndexOf("p")) + projectNo + docType.GetStringValue() + "01" + docExt.GetStringValue());
            }
            if (customer == Customer.Rewe) {
                fileName = Path.Combine(Settings.Default.PathRewe + jahr + @"\Artp\", jahr + rechnungsNummer.ToString("000") + docExt.GetStringValue());
                return fileName;
            }

            bool weiter = false;
            int maxRun = 0;
            while (weiter == false & maxRun < 98) {
                if (File.Exists(fileName)) {
                    fileName = ChangeDokNumber(fileName);
                    maxRun++;
                } else {
                    weiter = true;
                }
            }
            return fileName;
        }

        private static bool ExistsCustomsProperty(Worksheet sh, string propertyName) {
            foreach (CustomProperty prop in sh.CustomProperties) {
                if (prop.Name == propertyName) {
                    return true;
                }
            }
            return false;
        }

        public static string GetAfterSheetName(Microsoft.Office.Interop.Excel.Application exApp, int projectNumber, SheetType sheetType, Workbook wb, Logger log) {
            string ret = string.Empty;
            try {

                ProjektWorkbook myBook = new ProjektWorkbook(projectNumber, wb);

                switch (sheetType) {

                    #region Kalkulation

                    case SheetType.Kalkulation:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("k")) {
                                ret = sh.SheetName;
                            }
                        }
                        return ret;

                    #endregion

                    #region Angebot

                    case SheetType.Angebot:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Auftragsbestaetigung

                    case SheetType.Auftragsbestaetigung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Rechnung

                    case SheetType.Rechnung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("f")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("p")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Projektbericht

                    case SheetType.Projektbericht:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("b")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Abnahmeprotokoll

                    case SheetType.Abnahmeprotokoll:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("p")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Abrechnung

                    case SheetType.Abrechnung:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("r")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("f")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("p")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                    #region Lieferschein

                    case SheetType.Lieferschein:
                        foreach (ProjectSheet sh in myBook.ProjektSheets) {
                            if (sh.SheetName.Contains("l")) {
                                ret = sh.SheetName;
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("b")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("l")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & !Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (!sh.IsCalcSheet & sh.SheetName.Contains("a") & Tools.isNumeric(sh.SheetName.Right(1), System.Globalization.NumberStyles.Any)) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        if (ret == string.Empty) {
                            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                                if (sh.SheetName.Contains("k")) {
                                    ret = sh.SheetName;
                                }
                            }
                        }
                        return ret;

                    #endregion

                }
            } catch (Exception ex) {
                log.Add("GetAfterSheetName", ex);
            } finally {

            }
            return ret;
        }

        private static Worksheet GetCalcSheet(Workbook wb) {
            foreach (Worksheet sh in wb.Sheets) {
                ProjectSheet sheet = new ProjectSheet(sh);
                if (sheet.IsCalcSheet) {
                    return sh;
                }
            }
            return null;
        }

        private static DateTime GetDatumBis() {
            DateTime dBis;
            if (DateTime.Today.Day < 16) {
                dBis = new DateTime(DateTime.Today.AddMonths(2).Year, DateTime.Today.AddMonths(2).Month, 1);
                dBis = dBis.AddDays(-1);
            } else {
                dBis = new DateTime(DateTime.Today.AddMonths(3).Year, DateTime.Today.AddMonths(3).Month, 1);
                dBis = dBis.AddDays(-1);
            }
            return dBis;
        }

        private static int GetFirstFreeRowNumber(int copyRow, Worksheet sh) {
            Range row = sh.Rows[copyRow];
            Range r1 = row.Cells[1, 3];
            Range r2 = row.Cells[1, "N"];
            while (r1.Value != null && r2.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 3];
                r2 = row.Cells[1, "N"];
            }
            return copyRow;
        }

        private static Range GetFirstAeSubFreeRow(int copyRow, Worksheet sh) {
            Range row = sh.Rows[copyRow];
            Range r1 = row.Cells[1, 1];
            while (r1.Value != null) {
                copyRow++;
                row = sh.Rows[copyRow];
                r1 = row.Cells[1, 1];
            }
            return row;
        }

        private static string GetLastSheetName(Workbook wb) {
            string ret = string.Empty;
            foreach (Worksheet sh in wb.Sheets) {
                ret = sh.Name;
            }
            return ret;
        }

        private static int GetMaxProjectNo(Workbook wb) {
            List<string> p = new List<string>();
            int maxNr = 0;
            foreach (Worksheet sh in wb.Sheets) {
                if (!p.Contains(sh.Name.Substring(0, 2))) {
                    p.Add(sh.Name.Substring(0, 2));
                }
            }
            maxNr = p.Count;
            return maxNr;
        }

        private static DateTime GetNextDueDate(DateTime nowDate) {
            return GetNextDueDate(nowDate, 14);
        }

        private static DateTime GetNextDueDate(DateTime nowDate, int plusDays) {
            DateTime ret = nowDate;
            if (nowDate.DayOfWeek == DayOfWeek.Tuesday) {
                ret = nowDate.AddDays(-1);
            }
            if (nowDate.DayOfWeek == DayOfWeek.Wednesday) {
                ret = nowDate.AddDays(-2);
            }
            if (nowDate.DayOfWeek == DayOfWeek.Thursday) {
                ret = nowDate.AddDays(-3);
            }
            if (nowDate.DayOfWeek == DayOfWeek.Friday) {
                ret = nowDate.AddDays(-4);
            }
            if (nowDate.DayOfWeek == DayOfWeek.Saturday) {
                ret = nowDate.AddDays(-5);
            }
            if (nowDate.DayOfWeek == DayOfWeek.Sunday) {
                ret = nowDate.AddDays(-6);
            }
            ret = ret.AddDays(plusDays);
            return ret;
        }

        public static string GetNextSheetCharacter(string sheetType, Workbook wb, bool useCalcSheet) {
            char ret;
            List<Worksheet> countList = new List<Worksheet>();

            foreach (Worksheet sh in wb.Sheets) {
                ProjectSheet sheet = new ProjectSheet(sh);
                if (sh.Name.Contains(sheetType) & sheet.IsCalcSheet == useCalcSheet) {
                    countList.Add(sh);
                    if (sheetType == "a" & Tools.isNumeric(sh.Name.Right(1), System.Globalization.NumberStyles.Any)) {
                        countList.Remove(sh);
                    }
                }
            }
            ret = (char)(97 + countList.Count);
            return Convert.ToString(ret);
        }

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind) {
            return GetNextRowNumber(sh, rCol, activeCell, toFind, false);
        }

        private static int GetNextRowNumber(Worksheet sh, Range rCol, Range activeCell, object toFind, bool exact) {
            int ret = 0;
            Range r1;
            if (!exact) {
                r1 = rCol.Find(What: toFind, After: activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlPart);
            } else {
                r1 = rCol.Find(toFind, activeCell, SearchDirection: XlSearchDirection.xlNext, MatchCase: exact, LookAt: XlLookAt.xlWhole);
            }
            if (r1 != null) {
                ret = r1.Row;
            }
            return ret;
        }

        private static int GetNextSheetNumber(SheetType sheetType, int projectNumber, Workbook wb) {
            int ret = 0;
            ProjektWorkbook myBook = new ProjektWorkbook(projectNumber, wb);
            int countSheets = 0;

            foreach (ProjectSheet sh in myBook.ProjektSheets) {
                if (sh.SheetType == (int)sheetType) {
                    countSheets++;
                }
            }

            ret = countSheets + 1;
            return ret;
        }

        private static string GetPersonenkonto(string Kunde, bool isBusiness, Microsoft.Office.Interop.Excel.Application exApp, Logger log) {
            string ret = "";
            Workbook wb = null;

            try {
                string fileName = Path.Combine(Settings.Default.PathQs, "80da1.xlsx");
                if (!File.Exists(fileName)) {
                    log.Add("Datei " + fileName + " ist nicht vorhanden!");
                    ret = "Datei fehlt!";
                    return ret;
                }

                wb = exApp.Workbooks.Open(fileName, ReadOnly: true);

                if (!Tools.SheetsExists(wb, "Pkneu")) {
                    return ret;
                }

                Worksheet sh = wb.Sheets["Pkneu"];
                Range rCol;
                Range activeCell;
                int rowNumber = 0;
                object toFind = "";
                if (isBusiness) {
                    if (Kunde.Left(2) == "Wh") {
                        return "230 230";
                    }
                    if (Kunde.Left(2) == "Wg") {
                        return "230 901";
                    }
                    if (Kunde.Contains("Thyssen") | Kunde.Contains("Interlux")) {
                        return "230 090";
                    }
                    rCol = sh.Columns[2];
                    activeCell = sh.Cells[2, 2];
                    toFind = Kunde;
                    rowNumber = GetNextRowNumber(sh, rCol, activeCell, toFind);
                    if (rowNumber != 0) {
                        Range r1 = sh.Rows[rowNumber];
                        Range r2 = r1.Cells[1, "A"];
                        ret = ((string)Convert.ToString(r2.Value)).Left(3) + " " + ((string)Convert.ToString(r2.Value)).Right(3);
                        return ret;
                    }

                    return "230 900";
                }

                rCol = sh.Columns[2];
                activeCell = sh.Cells[2, 2];
                toFind = "Privatkunden " + Kunde.Left(1);
                rowNumber = GetNextRowNumber(sh, rCol, activeCell, toFind);
                if (rowNumber != 0) {
                    Range r1 = sh.Rows[rowNumber];
                    Range r2 = r1.Cells[1, "A"];
                    ret = ((string)Convert.ToString(r2.Value)).Left(3) + " " + ((string)Convert.ToString(r2.Value)).Right(3);
                    return ret;
                }

            } catch (Exception ex) {
                log.Add("GetPersonenkonto", ex);
                return ret;
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close();
                }
            }
            return ret;
        }

        private static int GetProjectNumber(string SheetName) {
            if (SheetName == null) {
                return 0;
            }

            int n1 = (int)(Tools.isNumeric(SheetName.Substring(1, 1), System.Globalization.NumberStyles.Any) ? Convert.ToInt32(SheetName.Substring(1, 1)) : 0);
            if (n1 != 0) {
                if (Tools.isNumeric(SheetName.Substring(2, 1), System.Globalization.NumberStyles.Any)) {
                    n1 = (n1 * 10) + Convert.ToInt32(SheetName.Substring(2, 1));
                }
            }

            return n1;
        }

        private static int GetRechnungsNummer(InvoiceObject i, string project, Microsoft.Office.Interop.Excel.Application exApp, Logger log) {
            Workbook wb = null;
            int ret = 0;
            log.Add("InvoiceObject: " + i.ToString());
            string fileName = Path.Combine(Settings.Default.PathRewe + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + @"\", "Ar" + i.Rechnungsdatum.ToString(Extensions.tpDateFormat).Left(2) + DocExt.XlsM.GetStringValue());
            //Testen
            //fileName = @"T:\Rewe\12\Ar12TestEs.xlsm";
            log.Add("FileName: " + fileName);

            try {
                if (Tools.FileIsInUse(fileName) | (!File.Exists(fileName))) {
                    log.Add("Datei " + fileName + " ist geöffnet oder nicht vorhanden!");
                    return -1;
                }

                wb = exApp.Workbooks.Open(fileName);

                if (!Tools.SheetsExists(wb, "Tp")) {
                    return -2;
                }

                Worksheet sh = wb.Sheets["Tp"];

                ProjektArSheet arSheet = new ProjektArSheet(sh);
                int newRowNr = 0;
                if (i.RechnungsNummer == 0) {
                    newRowNr = arSheet.GetNextFreeRowNumber(i.Rechnungsdatum.Year, i.Rechnungsdatum.Month, log);
                } else {
                    Range rCol = sh.Columns["M"];
                    Range aCell = sh.Cells[1, "M"];
                    object toFind = (i.RechnungsNummer - 1);
                    newRowNr = GetNextRowNumber(sh, rCol, aCell, toFind, true);
                    arSheet.NewInvoiceNumber = i.RechnungsNummer;
                }

                Range r1 = sh.Rows[newRowNr];
                InvoiceNumberRow newRow = new InvoiceNumberRow(r1);
                int newInvoiceNumber = arSheet.NewInvoiceNumber;

                newRow = new InvoiceNumberRow(r1);
                newRow.DebitorenKonto = i.PersonenKonto;
                newRow.Kunde = i.Kunde;
                newRow.Projekt = i.AFAbText;
                newRow.RechnungsDatum = i.Rechnungsdatum;
                newRow.Rechnungsempfänger = i.ReEmpfaenger;
                newRow.RechnungsNummer = newInvoiceNumber;
                newRow.SachKonto = i.SachKonto;
                newRow.SummeNetto = i.SummeNetto;
                newRow.Ust = i.Ust;
                newRow.VjProzent = i.AufwandVorjahr;

                if (i.AKontoBetrag != 0) {
                    r1 = sh.Rows[newRowNr + 1];
                    newRow = new InvoiceNumberRow(r1);
                    newRow.DebitorenKonto = "400 40";
                    newRow.Kunde = i.Kunde;
                    newRow.Projekt = i.AFAbText;
                    newRow.RechnungsDatum = i.Rechnungsdatum;
                    newRow.Rechnungsempfänger = i.ReEmpfaenger;
                    newRow.RechnungsNummer = newInvoiceNumber;
                    newRow.SachKonto = i.PersonenKonto;
                    newRow.Ust = (double)i.AKontoBetrag / (1 + (i.UstPercent / 100));
                    newRow.SummeNetto = (double)i.AKontoBetrag - ((double)i.AKontoBetrag / (1 + (i.UstPercent / 100)));
                    newRow.VjProzent = i.AufwandVorjahr;
                    newRow.SetBold();
                    
                }

                ret = arSheet.NewInvoiceNumber;


            } catch (Exception ex) {
                log.Add("GetRechnungsNummer", ex);
                return ret;
            } finally {
                if (wb != null) {
                    wb.Save();
                    wb.Close();
                }
            }
            return ret;
        }

        public static string GetSheetName(SheetType sheetType, int projectNumber, Workbook wb, Worksheet sh) {

            ProjectSheet sheet = new ProjectSheet(sh);
            string ret = "P" + sheet.ProjektNummer.ToString();
            switch (sheetType) {
                case SheetType.Kalkulation:
                    ret += "k" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case SheetType.Angebot:
                    ret += "a" + sheet.CalcNumber.ToString();
                    break;
                case SheetType.Auftragsbestaetigung:
                    ret += "a" + sheet.CalcCharacter;
                    break;
                case SheetType.Rechnung:
                    ret += "f" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case SheetType.Projektbericht:
                    ret += "b" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case SheetType.Abnahmeprotokoll:
                    ret += "p" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;
                case SheetType.Abrechnung:
                    ret += "r";
                    break;
                case SheetType.Lieferschein:
                    ret += "l" + GetNextSheetNumber(sheetType, projectNumber, wb).ToString();
                    break;

            }

            return ret;
        }

        public static TpMenu GetTpMenu(Worksheet sh, Logger log) {
            int colNo = 1;
            int row = 1;
            TpMenu myMenu = new TpMenu();
            int groupIndex = 1;
            int menuIndex = 0;

            try {

                // Lese alle Groupdaten ein !!!
                Range r1 = sh.Cells[row, "CE"];
                Range r2;
                string t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                if (t1 != "" | t1.Length > 0) {
                    TpMenuGroup g1 = new TpMenuGroup(t1, groupIndex);
                    myMenu.AddGroup(g1);
                    //Lese alle Gruppen ein
                    bool weiter = false;
                    while (!weiter) {

                        colNo = r1.Column + 1;
                        r1 = sh.Cells[1, colNo];
                        t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                        if (t1 != "") {

                            if (myMenu.GetMyGroupIndex(t1) == 0) {
                                groupIndex += 1;
                                g1 = new TpMenuGroup(t1, groupIndex);
                                myMenu.AddGroup(g1);
                            }
                        } else {
                            weiter = true;
                        }
                    }

                    //Lese alle Menüpunkte ein.
                    row = 2;
                    r1 = sh.Cells[row, "CE"];
                    colNo = r1.Column - 1;
                    weiter = false;
                    while (!weiter) {

                        colNo += 1;
                        r1 = sh.Cells[row, colNo];
                        t1 = Tools.Nz(Convert.ToString(r1.Value), "");
                        r2 = sh.Cells[row - 1, colNo];

                        if (t1 != "") {

                            int index = myMenu.GetMyGroupIndex(r2.Value);
                            if (myMenu.MyGroups[index - 1].GetMyMenuItemIndex(t1) == 0) {
                                menuIndex += 1;
                                TpMenuGroupItem gi1 = new TpMenuGroupItem(r2.Value, t1, colNo, menuIndex);
                                myMenu.MyGroups[index - 1].AddItems(gi1);
                            }
                        } else {
                            weiter = true;
                        }
                    }
                }
            } catch (Exception ex) {
                log.Add("GetTpMenu", ex);
            }
            return myMenu;
        }

        private static myOffice.Word.Document GetWordDocument(myOffice.Word.Application appWord, string fileName, string vorlage, Logger log) {
            myOffice.Word.Document myDoc = null;
            try {
                if (vorlage != null | vorlage != string.Empty) {
                    myDoc = appWord.Documents.Add(Template: vorlage);
                    return myDoc;
                } else {
                    myDoc = appWord.Documents.Add();
                    return myDoc;
                }
            } catch (Exception ex) {
                log.Add("GetWordDocument", ex);
            }
            return myDoc;
        }

        public static InvoiceObject GetNewInvoiceWithData(ProjectSheet inv, ProjectSheet abr, Organisation org, int rechnungsNummer, Microsoft.Office.Interop.Excel.Application exApp, Logger log) {

            TpBaseObjects.Global data = new TpBaseObjects.Global();

            InvoiceObject i = new InvoiceObject(rechnungsNummer);
            //Auftraggeberdaten
            i.ProjektBezeichnung = abr.Projekt;
            i.Auftraggeber = (org.Prefix != null ? org.Prefix + " " : "") + org.OrgName + " " + org.ZusätzeFW + " " + org.RechtsForm;
            i.Kunde = org.OrgName;
            i.CheckAuftraggeberPerson = false;
            i.MyPersons = org.MyPersons;
            i.AuftraggeberStrasse = org.Straße;
            i.AuftraggeberPlzOrt = org.Plz + " " + org.Ort;

            //Rechnungsempfänger - wird standardmäßig nicht befüllt - muss vom user ausgefüllt werden

            //Beilagen
            i.BeilageCheckLS = false;
            i.BeilageLSDate1 = new DateTime(1900, 1, 1);
            i.BeilageLSDate2 = new DateTime(1900, 1, 1);
            i.BeilageLSDate3 = new DateTime(1900, 1, 1);
            i.BeilageAPDate1 = new DateTime(1900, 1, 1);
            i.BeilageAPDate2 = new DateTime(1900, 1, 1);
            i.BeilageAPDate3 = new DateTime(1900, 1, 1);

            if (abr.LsDate1.Year > 1900)
                i.BeilageLSDate1 = abr.LsDate1;
            if (abr.LsDate2.Year > 1900)
                i.BeilageLSDate2 = abr.LsDate2;
            if (abr.LsDate3.Year > 1900)
                i.BeilageLSDate3 = abr.LsDate3;
            i.BeilageCheckAP = false;
            if (abr.ApDate1.Year > 1900)
                i.BeilageAPDate1 = abr.ApDate1;
            if (abr.ApDate2.Year > 1900)
                i.BeilageAPDate2 = abr.ApDate2;
            if (abr.ApDate3.Year > 1900)
                i.BeilageAPDate3 = abr.ApDate3;
            i.BeilageZusatzTextCheck = false;
            i.BeilageZusatzText = "";
            i.BeilageDateiCheck = false;
            i.BeilageDatei = "";

            //Rechnung
            i.Rechnungsdatum = DateTime.Now.Date;
            i.RechnungVersand = TpBaseObjects.Global.InvoiceSendType.Persoenlich;
            i.Ma = data.UserName;
            i.AufwandVorjahr = 0.0;
            i.Ust = inv.Umsatzsteuer;
            i.UstPercent = inv.UmsatzsteuerProzent;
            i.SummeBrutto = inv.SummeBrutto;
            i.SummeNetto = inv.SummeNetto;

            //Auftragsdaten
            i.AFAuftragTelefon = false;
            i.AFAuftragTelefonDatum = inv.AuftragsDatum;
            i.AFAuftragPers = false;
            i.AFAuftragPersDatum = inv.AuftragsDatum;
            i.AFAbCheck = false;
            i.AFAbDatum = abr.AuftragsBestaetigung;
            i.AFAbText = abr.Projekt;
            i.AFDurchfuehrungBis = Tools.NextGoodBusinessDay(DateTime.Now);
            i.AFDurchfuehrungVon = abr.DurchfuehrungVon;


            //Zahlung
            i.ZahlArt = TpBaseObjects.Global.InvoicePayType.BarNachMontage;
            i.AKontoDatum = inv.AkontoDatum;
            i.AKontoText = inv.AkontoBeleg;
            i.AKontoBetrag = inv.Akonto;
            //i.RechnungsBetrag = inv.SummeBrutto;
            i.SkontoProzent1 = inv.SkontoProzentSatz; // (inv.SkontoProzentSatz != 0 ? inv.SkontoProzentSatz : abr.SkontoProzentSatz);
            i.SkontoTage1 = inv.SkontoFrist; //(inv.SkontoFrist != 0 ? inv.SkontoFrist : abr.SkontoFrist);
            i.SkontoTage2 = inv.ZahlungsFrist; // (inv.ZahlungsFrist != 0 ? inv.ZahlungsFrist : abr.ZahlungsFrist);
            i.SkontoBetrag = inv.SkontoBetrag; // (inv.SkontoBetrag != 0 ? inv.SkontoBetrag : abr.SkontoBetrag);

            if (abr.Akonto != 0) {
                i.AKontoDatum = abr.AkontoDatum;
                i.AKontoText = abr.AkontoBeleg;
                i.AKontoBetrag = Math.Abs(abr.Akonto);
            }

            //Buchhaltung
            i.PersonenKonto = GetPersonenkonto(i.Kunde, i.IsBusiness, exApp, log);
            i.SachKonto = (i.Ust != 0 ? "400 00" : "");


            //Dateien
            LoadFiles(i, log);

            return i;
        }

        private static bool IsCalcSheet(Worksheet sh) {
            ProjectSheet sheet = new ProjectSheet(sh);
            return sheet.IsCalcSheet;
        }

        #endregion

    }
}
